db2: update multiple rows and field with a select

问题:

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

回答1:

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.

回答2:

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.

回答3:

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;

回答4:

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) ;

回答5:

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