CODEWITHSUNDEEP
sqlsql-server
Haris N I
Haris N I

Reputation: 6844

Why update of multiple tables in a single query is not possible?

I know the below query is wrong. SQL will not support this but want to know why SQL is not supporting this.

UPDATE Table1, Table2
SET Table1.Column1 = 'one',
    Table2.Column2 = 'two'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id and T1.id = 'id1'

Upvotes: 0

Views: 75

Answers (1)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

You can't update multiple tables in one statement, however, you can use a transaction to make sure that two UPDATE statements are treated atomically. You can also batch them to avoid a round trip.

BEGIN TRANSACTION;

UPDATE T1, T2
    SET T1.Column1 = 'one'        
    FROM Table1 T1, Table2 T2
    WHERE T1.id = T2.id and T1.id = 'id1'

UPDATE T1, T2
    SET T2.Column2 = 'two'
    FROM Table1 T1, Table2 T2
    WHERE T1.id = T2.id and T1.id = 'id1'

COMMIT;

Upvotes: 1

Related Questions