Update field in another table

[fatherofthree]

I am trying to update a field in a table with the value of a field in another table. One of the criterias set is where a value in a totally different table is in one of a list of codes. The code I created is below.

UPDATE DOCSADD SET DOCSADD.RET_NAME = (SELECT SETDATA.COMPANY FROM SETDATA WHERE SETDATA.TYPE = '3' AND SETDATA.AP_NUM = DOCSADD.AP_NUM) WHERE EXISTS (SELECT 1 FROM T, DOCSADD, SETDATA WHERE T.STATUS_CODE IN('1','2','3','5','6','7','10','13','14','15','18','19','20','21','22','23','24','25','26','27','28','29') AND T.AP_NUM = DOCSADD.AP_NUM AND T.AP_NUM = SETDATA.AP_NUM)

The problem I am having is the update is happening on all accounts no matter what the T.STATUS_CODE value is. Am I missing something in my code?

Thanks

Chad

 

[sem]

I don't see anything that restricts DOCSADD being updated. Note, that DOCSADD in UPDATE statement and DOCSADD in EXISTS query are completely independant. I'm not sure, but try to remove DOCSADD from EXISTS query.

Regards, Dima

 

[fatherofthree]

Dima,
I removed the DOCSADD from the EXISTS query and it still updates all records.

I want to restrict DOCSADD from being updated if T.STATUS_CODE IN('1','2','3',...)

Should that part of the code not be in the EXISTS query and be part of the UPDATE statement? If so, how?

Thanks

Chad

 

[sem]

Did you remove it from SELECT clause or also from WHERE clause? And what is T.AP_NUM = SETDATA.AP_NUM in EXISTS subquery for? Again, SETDATA in EXISTS is completely different from SETDATA in SET.

Regards, Dima

 

[fatherofthree]

I removed if from the EXISTS subquery only. I am trying to set the correlation between the T table and the SETDATA table by using T.AP_NUM - SETDATA.AP_NUM in the EXISTS subquery.

I originally didn't have anything in the EXISTS subquery except for:

(SELECT 1 FROM T WHERE T.STATUS_CODE IN('1','2','3','5','6','7','10','13','14','15','18','19','20','21','22','23','24','25','26','27','28','29'))

but that did the same thing as what is happening now so I thought the reason the code was not working was because there was no correlation between the T table and the other two tables.

I guess that was incorrect. I am lost on what to do from here. Can the T.STATUS_CODE criteria be included in the SET query?

Chad