CODEWITHSUNDEEP
sqldb2sql-updatesubqueryinner-join
P_user526
P_user526

Reputation: 75

DB2 Update statement for millions of rows

While working on DB2, I have 2 tables:

  1. TableA >

    2 columns: ClaimsId_A, CodeId_A

  2. TableB >

    2 columns: ClaimsId_B, CodeId_B

The goal is to write a DB2 Update statement that would check if ClaimsId_A is the same as ClaimsId_B, then replace CodeId_A (of that ClaimsID_A) with CodeId_B (of that ClaimID_B).

TableA and TableB both have millions of rows. VSch is the schema name.

The query I came up with doesn't seem to work:

UPDATE
    VSch.TableA
SET
    VSch.TableA.CodeId_A = VSch.TableB.CodeId_B
   FROM
    Vsch.TableA 
    INNER JOIN VSch.TableB
    on Vsch.TableA.ClaimsId_A = Vsch.TableB.ClaimsId_A;

Can someone please help me fix this query? If this can be done with just one Update query, that would be awesome!

Upvotes: 0

Views: 997

Answers (3)

Paul Vernon
Paul Vernon

Reputation: 3901

Since Db2 11.1, UPDATE FROM has been supported. See the examples at the end of the UPDATE manual page.

If your statement is returning a syntax error, you must be on an earlier version of Db2 (or using Db2 on a different platform such as Db2 of z, or Db2 for i)

Upvotes: 0

mshabou
mshabou

Reputation: 592

update support join, but you must write using update from:

update table a  set (a.codeid_a,a.col2,a.col3...) = (b.codeid_b,b.col2,b.col3...)  from tableb b  where b.claimsid_b = a.claimsid_a

PS1: table b, can be a subquery: from (select ...... ) b PS2: it is faster then update .... where exist (select 1...) because there is only one pass.

Upvotes: 0

GMB
GMB

Reputation: 222632

In DB2 in any platform, correlated subqueries should work:

update tablea a
set codeid_a = (select b.codeid_b from tableb b where b.claimsid_b = a.claimsid_a)
where exists (select 1 from tableb b where b.claimsid_b = a.claimsid_a);

Although you did not mention this possibility in your question, note that this will fail if some rows of a match on more than one row b.

Upvotes: 1

Related Questions