Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2007
    Posts
    3

    Unanswered: UPDATE updating too much

    I have two tables the main table, T1, has 1+ million rows and another table U1 with a few hundred rows. T1 has a primary key, K1 and a data column D1. Table U1 has updates to be applied to T1 and has columns K1 and D1 defined identically, except that U1 has an additional column D2 and the primary key is the combination of K1 and D2.

    Basically the task is to update T1.D1 for every match T1.K1 = U1.K1 where T1.D2 is some fixed value. To do this I wrote a correlated update query as:

    update T1 t
    set t.D1 = ( select u.D1
    from U1 u
    where t.K1 = u.K1
    and u.D2 = '123'
    );

    Running the query results in every row in T1 meeting the condition t.K1 = u.K1 with the appropriate value from U1. That's the good news. The bad news is that every other row in T1, those not meeting the condition t.K1 = u.K1, has the value of D1 now set to NULL!

    I am at a total loss as to why the NULLs are being set or what to do about it. Every variation of the query I can think of either does the same or fails syntactically.

    Any thoughts/suggestions on this are greatly appreciated.

    Robert

    "DB2 v9.1.0.2", "s070210", "U810940", and Fix Pack "2".
    Running in AIX 5.3
    Last edited by mad0dog; 06-03-07 at 23:26.

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    it is correct as
    there is no where clause on the update t table - so each record is updated
    and for those rows that do not find a match ; null is being set
    I am trying to figure out how to force a where clause on outer table for update to find only the matching records..
    I do not see any simple solution for the moment
    the easiest/fastest way would be a small sp that opens a cursor on t - executes the select on u and only executes the update if a match is found.
    Last edited by guyprzytula; 06-04-07 at 03:12.
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    How about:

    update T1 t
    set t.D1 = ( select u.D1
    from U1 u
    where t.K1 = u.K1
    and u.D2 = '123'
    )

    WHERE EXISTS
    ( select *
    from U1 u
    where t.K1 = u.K1
    and u.D2 = '123' )
    ;

  4. #4
    Join Date
    Jun 2007
    Posts
    3
    The addition of the WHERE clause worked great, thanks!

    Originally I was going to use a cursor to do the update but found that a curso could only be used in a stored procedure and then found that db2 does not support native sql stored procedures. I asked around this shop but no one I spoke to had ever written one. Reviewed the available docs and they were not exactly helpful. Any suggestions as to where to get a definitive example of generating, compiling and getting a stored procedure implemented in db2 would be appreciated. FWIW the system I'm developing in is AIX 5.3, DB2 9.1.

  5. #5
    Join Date
    Jun 2006
    Posts
    471
    stored procs can be written in sql procedure language and do not require C anymore
    some samples over here :
    http://www.ibm.com/developerworks/db...412greenstein/
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  6. #6
    Join Date
    Jun 2007
    Posts
    3
    Looks good , thanks!

Posting Permissions

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