How to update two tables in one statement in SQL Server 2005?
I want to update two tables in one go. How do I do that in SQL Server 2005?
UPD开发者_如何学运维ATE
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'
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;
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'
.
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)
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.
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.
You should place two update statements inside a transaction
You can write update statement for one table and then a trigger on first table update, which update second table
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
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
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'
精彩评论