0

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'
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Haris N I
  • 6,474
  • 6
  • 29
  • 35
  • 5
    Because those are the rules of SQL Server. You can wrap two separate `update` statements in a single transaction, which does the same thing. – Gordon Linoff Jul 02 '15 at 12:02
  • 2
    Note that the `UPDATE...FROM` SQL syntax is proprietary to SQL Server. ANSI SQL doesn't allow multiple tables to be updated in a single statement to begin with and the `FROM` extension is a SQL Server convenience feature. – Dan Guzman Jul 02 '15 at 12:08
  • 1
    [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged – marc_s Jul 02 '15 at 12:13
  • 1
    possible duplicate of http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 – kavetiraviteja Jul 02 '15 at 12:14
  • You can update one table on the base of another table value [enter link description here][1] [1]: http://dba.stackexchange.com/questions/21152/how-to-update-one-table-based-on-another-tables-values-on-the-fly – Dipak Yadav Jul 02 '15 at 12:14

1 Answers1

1

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;
Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32