I am using this querry which works fine;

Please advise me as to get an optimized queery as this scans the entire table for every update of a row.

UPDATE SRCHUPDATE_MAIN A
SET A.FULL_NAME =(SELECT A.FIRST_NAME || B.MIDDLE_NAME || C.LAST_NAME
FROM SRCHUPDATE_MID B,
SRCHUPDATE_LST C
WHERE A.EMP_ID = B.EMP_ID
AND A.EMP_ID = C.EMP_ID);

Thank you very much in anticipation of a solution

Regards

Padmanabhan

First, I assume that you have an index on EMP_ID in all three tables. If not, create one.

You will always do a tablespace scan on SRCHUPDATE_MAIN since you are updating every row in that table (with data from the other tables). So it is more efficient for DB2 to do a tablespace scan on this table.

If you have the indexes suggested above, there will be no tablespace scan on the subquery part of the SQL statement.

Make sure you do runstats command on all tables involved, and also on the indexes for these tables.

Thanks for your prompt reply.

Does DB2 support Update based on table join like the one given below?
If no, correlated sub query is the only solution?

MS SQL Sever:

UPDATE SRCHUPDATE_MAIN
SET FULL_NAME = A.FIRST_NAME + B.MIDDLE_NAME + C.LAST_NAME
FROM SRCHUPDATE_MAIN A, SRCHUPDATE_MID B, SRCHUPDATE_LST C
WHERE A.EMP_ID = B.EMP_ID
AND A.EMP_ID = C.EMP_ID

UPDATE SRCHUPDATE_MAIN E
SET FULL_NAME =
(SELECT A.FIRST_NAME || B.MIDDLE_NAME || C.LAST_NAME
FROM SRCHUPDATE_MAIN A, SRCHUPDATE_MID B, SRCHUPDATE_LST C
WHERE A.EMP_ID = B.EMP_ID
AND A.EMP_ID = C.EMP_ID
AND E.EMP_ID = A.EMP_ID)

The last line was added by me. Not sure if this fits your data correctly, but you need some way correlate the joined rows with the table you are updating.