Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52

    Unanswered: SQL Update Statement

    Hi,

    I have got 2 tables XX and YY. Now, I want to update the values in table YY (only for the matching IDs') from table XX based on "ID". I have used the following sql statement:

    update YY a set a.amt =
    (select b.amt from XX b where b.id = a.id)

    and found that it updated the matching values and set the values to null for the ID's that are not present in table YY i.e. for ID's 6 and 7. Any suggestions will be highly appretiated.

    Thanks.


    xx table
    --------
    ID AMT
    ---------- ----------
    1 123
    2 234
    3 345
    4 456
    5 567

    yy table
    --------
    ID AMT
    ---------- ----------
    1 987
    2
    3 457
    5
    6 257
    7 17

  2. #2
    Join Date
    Feb 2002
    Location
    Germany
    Posts
    141
    You're telling DB2 to update every row, so it does it correctly.
    Here two ways to change it:

    UPDATE yy
    SET amt = COALESCE((SELECT amt FROM xx WHERE xx.id=yy.id), amt)

    for every null, the value of amt won't be changed.

    UPDATE yy
    SET amt = (SELECT amt FROM xx WHERE xx.id = yy.id)
    WHERE EXISTS
    (SELECT 1 FROM xx WHERE xx.id = yy.id)

    Both should work and maybe someone give another one.

    HTH,
    Rodney Krick

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Though both of them work, I prefer the second one as only the rows which meet the criteria are updated .... This is important when updating only a few rows in a large table ...

    Cheers
    Sathyaram

    Originally posted by RKrick
    You're telling DB2 to update every row, so it does it correctly.
    Here two ways to change it:

    UPDATE yy
    SET amt = COALESCE((SELECT amt FROM xx WHERE xx.id=yy.id), amt)

    for every null, the value of amt won't be changed.

    UPDATE yy
    SET amt = (SELECT amt FROM xx WHERE xx.id = yy.id)
    WHERE EXISTS
    (SELECT 1 FROM xx WHERE xx.id = yy.id)

    Both should work and maybe someone give another one.

    HTH,
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52
    Dear Rodney,

    Thanks a lot both works fine. I also do appretiate Sathyaram's comment on the performance factor.

    Bye the way is the COALESCE function similar to NVL function in Oracle?

    Regards,
    Gautam Paul
    Last edited by gpeee; 03-10-04 at 06:05.

  5. #5
    Join Date
    Sep 2002
    Posts
    456
    Yes, it is.

    dollar

    Originally posted by gpeee
    Dear Rodney,

    Thanks a lot both works fine. I also do appretiate Sathyaram's comment on the performance factor.

    Bye the way is the COALESCE function similar to NVL function in Oracle?

    Regards,
    Gautam Paul

Posting Permissions

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