How to update two tables in one statement in SQL Server 2005

Question

I want to update two tables in one go  How do I do that in SQL Server 2005   UPDATE    Table1     Table2 SET    Table1 LastName  DR  XXXXXX      Table2 WAprrs  start stop  FROM    Table1 T1     Table2 T2 WHERE    T1 id   T2 id AND    T1 id    010008

User · Answer

You should place two update statements inside a transaction

User · Answer

The short answer to that is no   While you can enter multiple tables in the from clause of an update statement  you can only specify a single table after the update keyword   Even if you do write a  quot updatable quot  view  which is simply a view that follows certain restrictions   updates like this will fail   Here are the relevant clips from the MSDN documentation  emphasis is mine   UPDATE  Transact-SQL   The view referenced by table or view name must be updatable and reference exactly one base table in the FROM clause of the view  For more information about updatable views  see CREATE VIEW  Transact-SQL    CREATE VIEW  Transact-SQL   You can modify the data of an underlying base table through a view  as long as the following conditions are true   Any modifications  including UPDATE  INSERT  and DELETE statements  must reference columns from only one base table  The columns being modified in the view must directly reference the underlying data in the table columns  The columns cannot be derived in any other way  such as through the following   An aggregate function  AVG  COUNT  SUM  MIN  MAX  GROUPING  STDEV  STDEVP  VAR  and VARP  A computation  The column cannot be computed from an expression that uses other columns  Columns that are formed by using the set operators UNION  UNION ALL  CROSSJOIN  EXCEPT  and INTERSECT amount to a computation and are also not updatable    The columns being modified are not affected by GROUP BY  HAVING  or DISTINCT clauses  TOP is not used anywhere in the select statement of the view together with the WITH CHECK OPTION clause    In all honesty  though  you should consider using two different SQL statements within a transaction as per LBushkin s example  UPDATE  My original assertion that you could update multiple tables in an updatable view was wrong   On SQL Server 2005  amp  2012  it will generate the following error   I have corrected my answer to reflect this   Msg 4405  Level 16  State 1  Line 1 View or function  updatable view  is not updatable because the modification affects multiple base tables

User · Answer

You can t update two tables at once  but you can link an update into an insert using OUTPUT INTO  and you can use this output as a join for the second update   DECLARE  ids TABLE  id int   BEGIN TRANSACTION  UPDATE Table1  SET Table1 LastName    DR  XXXXXX    OUTPUT INSERTED id INTO  ids WHERE Table1 field    010008    UPDATE Table2  SET Table2 WAprrs    start stop   FROM Table2  JOIN  ids i on i id   Table2 id   COMMIT    I changed your example WHERE condition to be some other field than id  If it s id the you don t need this fancy OUTPUT  you can just UPDATE the second table for the same id  010008

User · Answer

You can write update statement for one table and then a trigger on first table update  which update second table

User · Answer

For general updating table1 specific colom based on Table2 specific colom  below query works perfectly    UPDATE table 1   SET Col 2   t2 Col2    Col 3   t2 Col3   FROM table1 t1   INNER JOIN table 2 t2 ON t1 Col1   t2 col1

User · Answer

From my perspective you can do this  its one to one update of two tables in SQL SERVER    BEGIN TRANSACTION        DECLARE  CNSREQ VARCHAR 30        DECLARE  ID INT       DECLARE  CNSRQDT VARCHAR 30        DECLARE  ID2 INT        DECLARE  IDCNSREQ INT       DECLARE  FINALCNSREQ VARCHAR 30        DECLARE  FINALCNSRQDT VARCHAR 30        DECLARE  IDCNSRQDT INT         SET  CNSREQ  SELECT MIN REQUISICIONESDT CNSREQ  FROM REQUISICIONESDT           INNER JOIN                REQUISICIONES                 ON REQUISICIONESDT CNSRQDT   REQUISICIONES CNSRQDT AND REQUISICIONES IDRQDT   REQUISICIONESDT ID            AND REQUISICIONES CNSREQ   REQUISICIONESDT CNSREQ AND REQUISICIONESDT IDREQ   REQUISICIONES ID         WHERE REQUISICIONES CNSRQDT   REQUISICIONES CNSRQDT AND REQUISICIONES IDRQDT   REQUISICIONESDT ID         SELECT REQUISICIONES CNSREQ  REQUISICIONES ID  REQUISICIONES CNSRQDT FROM REQUISICIONES        INNER JOIN            REQUISICIONESDT               ON REQUISICIONESDT CNSRQDT   REQUISICIONES CNSRQDT AND REQUISICIONES IDRQDT   REQUISICIONESDT ID                AND REQUISICIONES CNSREQ   REQUISICIONESDT CNSREQ AND REQUISICIONESDT IDREQ   REQUISICIONES ID         WHERE REQUISICIONES CNSRQDT   REQUISICIONES CNSRQDT AND REQUISICIONES IDRQDT   REQUISICIONESDT ID     AND REQUISICIONES CNSREQ    CNSREQ          UPDATE REQUISICIONESDT SET  REQUISICIONESDT CNSREQ NULL  REQUISICIONESDT IDREQ NULL           FROM REQUISICIONES INNER JOIN REQUISICIONESDT              ON REQUISICIONESDT CNSRQDT   REQUISICIONES CNSRQDT AND REQUISICIONES IDRQDT   REQUISICIONESDT ID        WHERE REQUISICIONES CNSRQDT   REQUISICIONES CNSRQDT AND REQUISICIONES IDRQDT   REQUISICIONESDT ID       AND REQUISICIONES CNSREQ    CNSREQ          UPDATE REQUISICIONES SET REQUISICIONES CNSRQDT NULL  REQUISICIONES IDRQDT NULL           FROM REQUISICIONES INNER JOIN REQUISICIONESDT           ON REQUISICIONESDT CNSRQDT   REQUISICIONES CNSRQDT AND REQUISICIONES IDRQDT   REQUISICIONESDT ID        WHERE REQUISICIONES CNSRQDT   REQUISICIONES CNSRQDT AND REQUISICIONES IDRQDT   REQUISICIONESDT ID      AND REQUISICIONES CNSREQ    CNSREQ         SET  ID2  SELECT MIN REQUISICIONESDT ID  FROM REQUISICIONESDT         WHERE ISNULL REQUISICIONESDT IDREQ 0  lt  gt 0       DELETE FROM REQUISICIONESDT WHERE REQUISICIONESDT ID  ID2         SET  IDCNSREQ  SELECT MIN  REQUISICIONES ID FROM REQUISICIONES           INNER JOIN REQUISICIONESDT ON         REQUISICIONESDT CEDULA   REQUISICIONES CEDULA AND REQUISICIONES FECHA SOLICITUD   REQUISICIONESDT FECHA SOLICITUD          WHERE REQUISICIONES CNSRQDT IS NULL AND REQUISICIONES IDRQDT IS NULL           SET  FINALCNSREQ  SELECT MIN  REQUISICIONES CNSREQ FROM REQUISICIONES             INNER JOIN REQUISICIONESDT ON         REQUISICIONESDT CEDULA   REQUISICIONES CEDULA AND REQUISICIONES FECHA SOLICITUD   REQUISICIONESDT FECHA SOLICITUD          WHERE REQUISICIONES CNSRQDT IS NULL AND REQUISICIONES IDRQDT IS NULL            SET  FINALCNSRQDT  SELECT MIN REQUISICIONESDT CNSRQDT  FROM REQUISICIONES            INNER JOIN REQUISICIONESDT ON           REQUISICIONESDT CEDULA   REQUISICIONES CEDULA AND REQUISICIONES FECHA SOLICITUD   REQUISICIONESDT FECHA SOLICITUD            WHERE REQUISICIONES CNSRQDT IS NULL AND REQUISICIONES IDRQDT IS NULL             SET  IDCNSRQDT  SELECT MIN  REQUISICIONESDT ID FROM REQUISICIONES            INNER JOIN REQUISICIONESDT ON          REQUISICIONESDT CEDULA   REQUISICIONES CEDULA AND REQUISICIONES FECHA SOLICITUD   REQUISICIONESDT FECHA SOLICITUD            WHERE REQUISICIONES CNSRQDT IS NULL AND REQUISICIONES IDRQDT IS NULL              UPDATE REQUISICIONES SET REQUISICIONES CNSRQDT    FINALCNSRQDT  REQUISICIONES IDRQDT  IDCNSRQDT FROM REQUISICIONES             INNER JOIN REQUISICIONESDT ON              REQUISICIONESDT CEDULA   REQUISICIONES CEDULA AND REQUISICIONES FECHA SOLICITUD   REQUISICIONESDT FECHA SOLICITUD             WHERE REQUISICIONESDT CNSRQDT    FINALCNSRQDT AND REQUISICIONESDT ID    IDCNSRQDT    ROLLBACK TRANSACTION

User · Answer

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 Table1   SET Table1 LastName    DR  XXXXXX   FROM Table1 T1  Table2 T2 WHERE T1 id   T2 id and T1 id    011008    UPDATE Table2 SET Table2 WAprrs    start stop  FROM Table1 T1  Table2 T2 WHERE T1 id   T2 id and T1 id    011008    COMMIT

User · Answer

Sorry   afaik  you cannot do that   To update attributes in two different tables  you will need to execute two separate statements   But they can be in a batch   a set of SQL sent to the server in one round trip

User · Answer

This works for MySQL and is really just an implicit transaction but it should go something like this   UPDATE Table1 t1  Table2 t2 SET  t2 field   t2 field 2  t1 field   t1 field 2  WHERE t1 id   t2 foreign id and t2 id    123414    if you are doing updates to multi tables that require multi statements    which is likely possible if you update one  then another based on other conditions    you should use a transaction

User · Answer

It is as simple as this query shown below   UPDATE    Table1 T1 join Table2 T2 on T1 id   T2 id SET    T1 LastName  DR  XXXXXX      T2 WAprrs  start stop  WHERE    T1 id    010008

User · Answer

You can update two tables in one query  UPDATE Table1  Table2  SET Table1 LastName  quot DR  XXXXXX quot   Table2 WAprrs  quot start stop quot   WHERE Table1 id Table2 id AND Table1 id  quot 010008 quot

[sql] How to update two tables in one statement in SQL Server 2005?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql