Update

[rekhatr10]

Hi everyone

How do I update 2 table at the same time? I have to move records from 2 table to a new acct # in the same table.

The fileds are

Table1 Table2
Acct Acct
Docid docid
idsubtype idsubtype
revvalue
userid

Based on acct number I have to change the the acct number from 1 to 2 and the revvalue should be 'R' for reviwed.

I was using
Update set account = 2 where account =1 and then go the table 2 join them on docid and idsubtype and used the folowing update sql statment
update set account = 2 where images.account = 2 and review.account = 1 it errored out.
Any suggestions would be helpful.

Thank you
Rekha

 

[JBaileys]

Your update statement is missing the join between the tables. For clarity, you should consider spelling out the fields as table.field.

Therefore - your update statement should look something like...

UPDATE table1
SET t1.field = 1
FROM table1 t1, table2 t2
WHERE t1.field2 = t2.field3