how to update multiple columns in multiple tables

Question

How can we update multiple columns in multiple tables in a single query?

I tried the below but no success.

update Class c, School s
set c.Status='Absent', s.Status='Absent'
Where c.ID='&ID' and c.ID=s.ID;

Any clues are appreciable.

Possible duplicate of [MySQL, update multiple tables with one query](http://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query) — ADyson, Apr 07 '17 at 09:22
there are lots of answers to this if you search, including the duplicate I've just suggested. — ADyson, Apr 07 '17 at 09:22
Your current query looks correct. Have you actually tried running it? — Tim Biegeleisen, Apr 07 '17 at 09:24
Yes, but i am getting an error like 'ORA-00971: missing SET keyword' — King, Apr 07 '17 at 09:30
You are missing a single quote in your query near c.Status='Absent, s.Status='Absent' — Saili Jaguste, Apr 07 '17 at 09:39
retracted my "marked as duplicate" vote, since OP had incorrectly tagged the question with MySQL instead of Oracle. A bit worrying, for someone whose profile gives their job title as "DB Engineer", that they cannot identify the DBMS they are using. — ADyson, Apr 07 '17 at 15:07

score 0 · Answer 1 · answered Apr 07 '17 at 11:03

You can't do a direct update of multiple tables in Oracle.

You might be able to do an update of a view, like this:

update (
select c.status class_status, s.status school_status
from Class c, School s
Where c.ID='&ID' and c.ID=s.ID )
set class_status='Absent', school_status='Absent'

Oracle will only allow this if the join between the tables preserves keys - i.e. each row produced by the join directly maps to a single row in the source table(s) being updated. I suspect that in this case that School is not key-preserved as each row in that table probably relates to more than one row in Class. So you won't be able to do this in a single statement.

Oracle is far less tolerant of fuzzy relational thinking than other systems.

how to update multiple columns in multiple tables

1 Answers1