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

SqlSql ServerSql Server-2005Tsql

Sql Problem Overview

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'

Sql Solutions

Solution 1 - Sql

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;

Solution 2 - Sql

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'.

Solution 3 - Sql

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)

Solution 4 - Sql

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 "updatable" 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 & 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.

Solution 5 - Sql

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. 

Solution 6 - Sql

You should place two update statements inside a transaction

Solution 7 - Sql

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

Solution 8 - Sql

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)<>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

Solution 9 - Sql

For general updating table1 specific column based on Table2 specific column, this 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

Solution 10 - Sql

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'
Categories
Recommended Sql Solutions
Recommended Sql Server Solutions
Recommended Sql Server-2005 Solutions
Recommended Tsql Solutions

Previous Solution:

Differences between fork and exec

CUnixForkExec

Next Solution:

Where to place and how to read configuration resource files in servlet based application?

ServletsJakarta EeConfigurationResourcesProperties File

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionJangoView Question on Stackoverflow
Solution 1 - SqlLBushkinView Answer on Stackoverflow
Solution 2 - SqlRemus RusanuView Answer on Stackoverflow
Solution 3 - SqlCharles BretanaView Answer on Stackoverflow
Solution 4 - SqljveazeyView Answer on Stackoverflow
Solution 5 - Sqluser3662407View Answer on Stackoverflow
Solution 6 - SqlMickView Answer on Stackoverflow
Solution 7 - SqlSandip - Frontend DeveloperView Answer on Stackoverflow
Solution 8 - SqlRicardo RoaView Answer on Stackoverflow
Solution 9 - SqlAbdul KhaliqView Answer on Stackoverflow
Solution 10 - SqlMohit GuptaView Answer on Stackoverflow