UPDATE with subquery ?

oldnickj · #1 (**permalink**) 02-04-09, 13:53

For the life of me this query looks right but returns "0 row(s) affected" and I know there are 157 records which should match!

Nick

update CBA_Copy SET access_usr = 3
WHERE access_usr IS NULL AND CBA_ID =( SELECT MAX(CBA_ID) FROM DUES_PAID GROUP BY CBA_ID)

r937 · #2 (**permalink**) 02-04-09, 15:02

in your subquery, you have GROUP BY cba_id

this means it will produce one aggregate row per cba_id

(that's what GROUP BY goes -- it aggregates a bunch of detail rows into one aggregate row)

naturally, for each distinct cba_d, the max(cba_id) is going to be that value!!!

it would be like asking what is the maximum employee id for each employee, where each employee has only one employee id

perhaps you could explain in words what you're trying to do?

oldnickj · #3 (**permalink**) 02-04-09, 15:32

In the CBA Table I need to set the access level to 3 if 1. the current access level is NULL and 2.the key(CBA_ID) has is listed in the DUES_PAID table. DUES PAID has multiple entries for this foreign key, one for each year they subscribed.

If there are no entries for the CBA_ID in the Dues_paid table then I need to set the access level to 2

Thanks
Nick

update CBA_Copy SET access_usr = 3
WHERE access_usr IS NULL AND CBA_ID =( SELECT MAX(CBA_ID) FROM DUES_PAID GROUP BY CBA_ID)

mike_bike_kite · #4 (**permalink**) 02-04-09, 16:06

Would this be a better way of writing it?

Code:

update CBA_Copy 
SET    access_usr = 3
WHERE  access_usr IS NULL 
       AND exists( 
             select  1
             from    DUES_PAID dp
             where   dp.CBA_ID = CBA_Copy.CBA_ID )

You could set the access_usr to 2 in this SQL as well but it gets messy then.