CODEWITHSUNDEEP
sqlsql-updatedb2
user3835327
user3835327

Reputation: 1204

Update SQL query by comparing two table

How to update query in sql column by comparing two tables ? This might be duplicated question, but yet still cannot solve my problem. Any help would be appreciated.

What i've tried so far, but error

UPDATE b SET b.STAMP = b.STAMP + 10 FROM TB_FWORKERSCH b,TB_FWORKERCN a 
WHERE a.ISSDATE>='20150401' AND a.UKEY=b.UKEY2 and b.STAMP=0 AND b.IG_SUMINS!=0

DB2 Database

Upvotes: 0

Views: 84

Answers (2)

user330315
user330315

Reputation:

DB2 doesn't allow a JOIN or FROM for an UPDATE clause (this is also not specified in the SQL standard).

You can achieve what you want with a co-related sub-query:

UPDATE tb_fworkersch b
  SET stamp = stamp + 10 
WHERE EXISTS (SELECT 1 
              FROM tb_fworkercn a 
              WHERE a.issdate >= '20150401' 
                AND a.ukey = b.ukey2) 
AND b.stamp = 0
AND b.ig_sumins <> 0

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

Try this:

MERGE INTO TB_FWORKERSCH b 
USING TB_FWORKERCN a
   ON a.UKEY=b.UKEY2
   AND a.ISSDATE>='20150401' AND b.STAMP=0 AND b.IG_SUMINS<>0
WHEN MATCHED
   THEN UPDATE SET b.STAMP = b.STAMP + 10;

Upvotes: 0

Related Questions