我想一次更新两个表。如何在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'
当前回答
对于基于Table2特定列的table1特定列的常规更新，这个查询工作完美:
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
其他回答
你不能一次更新两个表，但是你可以使用OUTPUT into将一个更新链接到一个插入，并且你可以使用这个输出作为第二次更新的连接:
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;
我将示例WHERE条件更改为id以外的其他字段。如果它是id，你不需要这个花哨的输出，你可以更新相同id='010008'的第二个表。
简短的回答是否定的。虽然可以在更新语句的from子句中输入多个表，但只能在update关键字后指定单个表。即使您确实编写了一个“可更新”视图(它只是一个遵循某些限制的视图)，像这样的更新也会失败。以下是来自MSDN文档的相关片段(重点是我的)。
更新(transact - sql)
table_or_view_name引用的视图必须是可更新的，并且在视图的FROM子句中只引用一个基表。有关可更新视图的更多信息，请参见CREATE VIEW (Transact-SQL)。
创建视图(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.
但是，坦率地说，您应该考虑在一个事务中使用两个不同的SQL语句，就像LBushkin的例子一样。
更新:我最初认为可以在可更新视图中更新多个表的断言是错误的。在SQL Server 2005 & 2012上，它将生成以下错误。为了反映这一点，我修改了我的回答。
编号4405,16层，状态1号线 视图或函数'updatable_view'是不可更新的，因为修改会影响多个基表。
从我的角度来看，你可以这样做，这是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
您不能在一条语句中更新多个表，但是，您可以使用一个事务来确保两个update语句被原子地处理。您还可以批量处理它们以避免往返。
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;
对于基于Table2特定列的table1特定列的常规更新，这个查询工作完美:
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
