x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

DB2 SET UPDATE

No cursors please!  DB2 syntax varies from SQL Server.

Just because it took me an hour to find it....I wish the syntax would be the same...but they're not

 UPDATE ASSESS_AUDIT A1                                   
        SET FIRST_NAME = (SELECT SUB_FIRST_NAME           
                            FROM ORG_TREE O1              
                           WHERE A1.EMPL_ID = O1.EMPL_ID) 
 WHERE FIRST_NAME = ' '                                  
   AND EXISTS (SELECT * FROM ORG_TREE O2                 
                WHERE A1.EMPL_ID = O2.EMPL_ID);          

Another example

UPDATE SS_FAMILY_TREE_MAP SFTM

SET ORG_NAME = ( SELECT SUBSTR(SOR.SS_ORG_NAME,1,25)

FROM SS_ORG_REL SOR

WHERE SFTM.SS_ORG_CD = SOR.SS_ORG_CD

AND SFTM.SS_STRUCTURE_NAME = SOR.STRUCTURE_NAME)

WHERE ORG_NAME IS NULL OR ORG_NAME = ' '

;

Print | posted on Wednesday, October 20, 2004 3:58 PM | Filed Under [ DB2 ]

Feedback

Gravatar

# re: DB2 SET UPDATE

thank you
12/1/2005 3:27 AM | nandika ekanayake
Gravatar

# re: DB2 SET UPDATE

thanks for sharing your solution...
7/15/2006 4:20 PM | Rajesh
Gravatar

# re: DB2 SET UPDATE

Thanks so much for the example syntax. Lucky you came along, the DB2 documentation has such lame examples of update statements, mostly showing things like Update table set ColumnName = 'Value'. Not very helpful. You've pulled my bacon from the fire.

-Larry Smith
11/15/2006 1:42 PM | Larry Smith
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET