Db2: update multiple rows and field using selection in another table

Question

it is possible to increase the field a and b of the table (Aa and Ab) using the values ​​c and d of another table (Bc Bd) for all rows A, where Ax == Bz?

I'm crazy with this request

Answer 1

DB2 and the SQL standard do not have a FROM clause in an UPDATE statement. Therefore, you need to clearly separate the steps from

• identify the lines to be changed, and
• calculate 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 can 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 subqueries in the SET and FROM clauses are identical and should combine them in the internal execution plan.

Answer 2

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

 MERGE INTO A USING (SELECT c, d, z from B) B ON (Ax = Bz) WHEN MATCHED THEN UPDATE SET Aa = Aa + Bc, Ab = Ab + Bd; 

Read more about MERGE here .

Answer 3

Tested in DB2 10.6

Almost the same as @jhnwsk, but with the addition of short table abbreviations.

 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; 

Answer 4

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