Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: UPDATE with subquery ?

    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)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    124
    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)

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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.

  5. #5
    Join Date
    Jan 2009
    Posts
    124
    Exists? totally new to me but I found the reference in the very fine new book "Simply SQL" by Rudy Limeback.

    I'll give it a try! Thanks

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by oldnickj
    Exists? totally new to me but I found the reference in the very fine new book "Simply SQL" by Rudy Limeback.
    Never heard of him

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •