CODEWITHSUNDEEP
sqldb2
lucaconlaq
lucaconlaq

Reputation: 1541

db2: update multiple rows and field with a select on a different table

is it possible to increment the field a and b of a table (A.a and A.b) using the value c and d of a different table (B.c B.d) for all the row of A where A.x == B.z?

I'm getting crazy with this query

Upvotes: 13

Views: 99090

Answers (5)

Vlad Troyan
Vlad Troyan

Reputation: 157

This one also works quite good

update TableA A set a = (select a from TableB B where A.x = B.z) where exists (select 1 from TableB B where A.x = B.z) ;

Upvotes: 1

Tim Seed
Tim Seed

Reputation: 5289

Tested under DB2 10.6

Practically the same as @jhnwsk, but with added table short cuts. 

Merge into "PRODUCTION"."COUNTRY" as N
      using (SELECT OD.NAME,OD.KEY from "DEVELOPMENT"."COUNTRY" as OD) as O
      on (O.KEY = N.KEY)
      WHEN MATCHED THEN
           UPDATE SET N.NAME=O.NAME;

Upvotes: 1

FELIPE
FELIPE

Reputation: 1

UPDATE CS70P t1 
 SET (T1.TIPOCRE) = (
  SELECT MOROSO
  FROM  FCSALDOC t2 ,CS70P t1
  WHERE t1.NUMCRE =t2.CODCTA
 )

Upvotes: -2

jhnwsk
jhnwsk

Reputation: 953

Yes it is possible. You can try something like this:

MERGE INTO A
USING (SELECT c, d, z from B) B
ON (A.x = B.z)
WHEN MATCHED THEN
UPDATE SET A.a = A.a + B.c, A.b = A.b + B.d;

You can read more about MERGE here.

Upvotes: 8

aF.
aF.

Reputation: 66757

DB2 and the SQL standard don't have a FROM clause in an UPDATE statement. So you have to clearly separate the steps to

  1. identify the rows to be modified and to
  2. compute the new value.

.

Here is an example:

UPDATE TABLE A
SET A.FLD_SUPV = ( SELECT B.FLD_SUPV
FROM TABLEA A, TABLEB B, TABLEC C,TABLED D
WHERE A.FLD1= B.FLD1
AND A.FLD_DT >= B.FLD_FM_DT
AND A.FLD_DT <= B.FLD_THRU_DT
AND A.FLD_DT > D.FLD_THRU_DT
AND A.FLD_DT < C.FLD_EFF_DT )
WHERE EXISTS ( SELECT B.FLD_SUPV
FROM TABLEA A, TABLEB B, TABLEC C,TABLED D
WHERE A.FLD1= B.FLD1
AND A.FLD_DT >= B.FLD_FM_DT
AND A.FLD_DT <= B.FLD_THRU_DT
AND A.FLD_DT > D.FLD_THRU_DT
AND A.FLD_DT < C.FLD_EFF_DT )

To update two fields you may use an example like this:

UPDATE table1 t1 
 SET (col1, col2) = (
  SELECT col3, col4 
  FROM  table2 t2 
  WHERE t1.col8=t2.col9
 )

The optimizer will see that the sub-queries in the SET and the FROM clause are identical and it should merge them in the internal execution plan.

Upvotes: 11

Related Questions