Update columns in multiple tables simultaneously

[wickyd]

Hi folx

I have 6 tables to update. Each table has a different column name, but the record in that column is identical.

I am looking for one sql query that can do the following:

update invoices1 set code = 'abc123' where code = 'abc456'
update invoices2 set itemno = 'abc123' where itemno = 'abc456'
update invoices3 set stockno = 'abc123' where stockno = 'abc456'
update invoices4 set codeno = 'abc123' where codeno = 'abc456'
update invoices5 set itemcode = 'abc123' where itemcode = 'abc456'
update invoices6 set stockcode = 'abc123' where stockcode = 'abc456'


Thank you.

Kind regards
wickyd

 

[swampBoogie]

Update can be used to update one table only.

Does each of these columns constitute the primary key?

 

[wickyd]

The key is primary in only one table.

 

[ETolent]

You might want to try creating a view (WITH CHECK OPTION)via table join and see what happens. I just thought it might work.

 

[swampBoogie]

Why do you need to do this? Are the other columns foreign keys to the first? In that case you can set on update cascade in your foreign key definition.