update with join of multiple tables

[beena]

Apologize for posting this question....
Yes there were postings on update with join....
My question involves 4 table join... (hopefully qualifies as a new
question)

Need to convert the following sql from SYbase to UDB 8.2 FP8 on AIX.

UPDATE TABLE A
SET A.FLD_SUPV = 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

Any suggestions... Thanks in advance....

[Knut Stolze]

beena wrote:

Apologize for posting this question....
Yes there were postings on update with join....
My question involves 4 table join... (hopefully qualifies as a new
question)

Need to convert the following sql from SYbase to UDB 8.2 FP8 on AIX.

UPDATE TABLE A
SET A.FLD_SUPV = 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

DB2 and the SQL standard don't have a FROM clause in an UPDATE statement.
So you have to clearly separate the steps to (a) identify the rows to be
modified and to (b) compute the new value.

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 )

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.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Serge Rielau]

MERGE INTO TABLEA A
USING (SELECT B.FLD_SUPV
FROM 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) AS S
ON 1=1
WHEN MATCHED THEN
UPDATE SET A.FLD_SUPV = S.FLD_SUPV

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab