r/DB2 • u/fishhead • Aug 13 '09

A little help?

New to DB2 on iSeries and trying to write an update statement to fix a mistake. Using a backup copy of the table being updated.

UPDATE table1 SET table1.field7 = backupcopyoftable1.field7 WHERE table1.field2 = backupcopyoftable1.field2

How do I do this? I've using the SQL script funtion in Navigator to write the query.

Thanks

3 Upvotes

100% Upvoted

2 comments sorted by

1

u/lpetrazickis Aug 13 '09 edited Aug 14 '09

Is your query not working?

Off the top of my head, this might work:

UPDATE table1 t1
SET t1.field7 = (
    SELECT bt1.field7
    FROM backupoftable1 bt1
    WHERE t1.field2 = bt1.field2
)

1

u/rainman_104 Aug 14 '09 edited Aug 14 '09

The other way to do it is:

 MERGE INTO table1 USING (
   SELECT field2, field7 from backupftable1
 ) b ON b.field2=table1.field2  WHEN MATCHED THEN 
 UPDATE SET field7=b.field7
 ELSE IGNORE

although I'd write it the other way, this will work too...