CODEWITHSUNDEEP
ibm-midrangedb2-400
MarkSatACE
MarkSatACE

Reputation: 11

I am trying to use the UPDATE SELECT statement on DB2 for IBM i (AS400)

Can anyone help me out with an UPDATE SELECT statement on DB2 for i (formerly known as the AS400)? I am trying to update one table based upon the info. in another table. Thank you!

Upvotes: 1

Views: 1359

Answers (1)

Charles
Charles

Reputation: 23793

UPDATE tbla A
SET cola = (SELECT colb
             FROM tblb B
            WHERE a.key = b.key
           )

The above assumes that either - There's a row in tblb for every key in tbla - or cola allows NULL values

If the assumption is false, you'll get an error about cola not allowing NULL.

You'll need to either use COALESCE to provide for a default or not attempt to update rows in tbla that don't have a match in tblb.

Lastly, DB2 for i allows Row-Value-Expressions, so the following is also valid:

UPDATE tbla A
SET (cola1, cola2) 
         = (SELECT colb1, colb2
             FROM tblb B
            WHERE a.key = b.key
           )

Upvotes: 1

Related Questions