#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    1
    Rep Power
    0

    Help with SQL Query


    Given the following tables, how would I set the status in TableA to 'S' only if *all* of the entries in TableB have the same value for amountclaim and amountpay? Thanks!

    TableA
    +------+--------+
    | id | status |
    +------+--------+
    | 1432 | B |
    +------+--------+


    TableB
    +-------------+------------+-------------+-----------+
    | radetail_no | billing_no | amountclaim | amountpay |
    +-------------+------------+-------------+-----------+
    | 4655 | 1432 | 6.60 | 6.60 |
    | 4656 | 1432 | 4.45 | 4.45 |
    | 4657 | 1432 | 74.55 | 74.55 |
    | 4658 | 1432 | 74.10 | 74.10 |
    | 4659 | 1432 | 44.00 | 44.00 |
    | 4660 | 1432 | 36.90 | 36.90 |
    +-------------+------------+-------------+-----------+
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Code:
    UPDATE tablea
       SET status =
           CASE WHEN ( SELECT COUNT(DISTINCT amountclaim) FROM tableb ) = 1
                 AND ( SELECT COUNT(DISTINCT amountpay) FROM tableb ) = 1
                THEN 'S' 
                ELSE status END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,576
    Rep Power
    1906
    I think OP want to check, for each row in table B, that the value for amountclaim equals the value for amountpay.
    Then, if all rows with same billing_no is equals, update table with a 'S' where id is equal to the billng_no.

    Something like below, which is an idea based on on my thought and is not tested:
    Code:
    UPDATE tableA
    	SET status =
    		CASE WHEN
    			(SELECT billing_no as bno
    			 FROM TableB
    			 GROUP BY billing_no
    			 HAVING SUM(amountclaim) = SUM(amountpay)) = tableA.id 
    			THEN 'S'
    			ELSE status END
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    that subquery has to be a scalar subquery (i.e. exactly 1 row, exactly 1 column) and it isn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Thank you for the response Rudy. I tried to put together my own query and came up with the one shown below. I think I'm close but, unfortunately, it doesn't update any rows. Can you see what I'm doing wrong?

    UPDATE TableA SET status='S'
    WHERE status = 'B'
    AND (select sum(amountclaim) FROM TableB WHERE billing_no=TableA.id) = (select sum(amountpay) FROM TableB where billing_no=TableA.id)
    AND TableA.id=1432;
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    just a wild guess, but are you using decimal or float for those columns?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    The amountclaim and amountpay fields both store dollar values but they are varchar(8) fields.
  14. #8
  15. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,576
    Rep Power
    1906
    Can you change the type to Decimal instead of Varchar? This will be preferred.

    Otherwise you will have to perform a CAST to DECIMAL before doing any SUM.
    for example:
    sql Code:
    SUM(CAST(amountclaim AS DECIMAL(10,2)))
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    3
    Rep Power
    0
    Unfortunately I have no control over the fields. Is that the only thing preventing my query from working?
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by wwwkris2
    Unfortunately I have no control over the fields.
    the guy who decided to store numeric data in varchar columns should be shot

    without CAST, the varchars are probably being converted to floats (i'm guessing)

    try the CAST AS DECIMAL solution, it should work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo