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