更新 SQL 中的多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9079617/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update multiple columns in SQL
提问by Joe
Is there a way to update multiple columns in SQL server the same way an insert statement is used?
有没有办法像使用插入语句一样更新 SQL Server 中的多个列？
Something like:
就像是：
Update table1 set (a,b,c,d,e,f,g,h,i,j,k)=
(t2.a,t2.b,t2.c,t2.d,t2.e,t2.f,t2.g,t2.h,t2.i,t2.j,t2.k)
from table2 t2
where table1.id=table2.id
Or something like that, rather than like so:
或者类似的东西，而不是这样：
update table set a=t2.a,b=t2.b etc
which can be pretty tiresome to write if you have 100+ columns.
如果你有 100 多列，写起来会很累。
采纳答案by gbn
The "tiresome way" is standard SQL and how mainstream RDBMS do it.
“令人厌烦的方式”是标准 SQL 以及主流 RDBMS 如何做到这一点。
With a 100+ columns, you mostly likely have a design problem... also, there are mitigating methods in client tools (eg generation UPDATE statements) or by using ORMs
有 100 多列，您很可能会遇到设计问题……此外，客户端工具（例如生成 UPDATE 语句）或使用 ORM 也有缓解方法
回答by marc_s
Try this:
尝试这个：
UPDATE table1
SET a = t2.a, b = t2.b, .......
FROM table2 t2
WHERE table1.id = t2.id
That should work in most SQL dialects, excluding Oracle.
这应该适用于大多数 SQL 方言，不包括 Oracle。
And yes - it's a lot of typing - it's the way SQL does this.
是的 - 需要大量输入 - 这就是 SQL 执行此操作的方式。
回答by Alex K.
The Update table1 set (a,b,c) = (select x,y,x)syntax is an example of the use of
row-value constructors, Oracle supports this, MSSQL does not. (Connect item)
该Update table1 set (a,b,c) = (select x,y,x)语法是使用行值构造的一个例子，Oracle支持此，MSSQL没有。（连接项目）
回答by John Woo
Your query is nearly correct. The T-SQL for this is:
您的查询几乎是正确的。用于此的 T-SQL 是：
UPDATE Table1
SET Field1 = Table2.Field1,
Field2 = Table2.Field2,
other columns...
FROM Table2
WHERE Table1.ID = Table2.ID
回答by betrice mpalanzi
Syntax
句法
UPDATE table-name
SET column-name = value, column-name = value, ...
WHERE condition
Example
例子
UPDATE school
SET course = 'mysqli', teacher = 'Tanzania', student = 'you'
WHERE id = 6
回答by Thangamani Palanisamy
UPDATE t1
SET
t1.a = t2.a,
t1.b = t2.b,
.
.
.
FROM
table1 t1
INNER JOIN table2 t2 ON t1.id=t2.id
You can try this
你可以试试这个
回答by Peter
I tried with this way and its working fine :
我尝试过这种方式并且它工作正常：
UPDATE
Emp
SET
ID = 123,
Name = 'Peter'
FROM
Table_Name
回答by Mohamed Bekheit
If you need to re-type this several times, you can do like I did once. Get your columns` names into rows in excel sheet (write down at the end of each column name (=) which is easy in notepad++) on the right side make a column to copy and paste your value that will correspond to the new entries at each column. Then on the right of them in an independent column put the commas as designed
如果你需要重新输入几次，你可以像我曾经做过的那样。将您的列名放入 excel 表中的行中（在每个列名的末尾写下（在记事本++中很容易））在右侧制作一列以复制和粘贴您的值，该列将对应于新条目每列。然后在它们的右侧在一个独立的列中按设计放置逗号
Then you will have to copy your values into the middle column each time then just paste then and run
然后你每次都必须将你的值复制到中间列然后粘贴然后运行
I do not know an easier solution
我不知道更简单的解决方案
回答by Dragos Custura
here is one that works:
这是一个有效的方法：
UPDATE `table_1`
INNER JOIN
`table_2` SET col1= value, col2= val,col3= val,col4= val;
value is the column from table_2
值是 table_2 中的列
回答by Weihui Guo
I'd like to share with you how I address this kind of question. My case is slightly different as the result of table2 is dynamic and the column numbers may be less than that of table1. But the concept is the same.
我想与您分享我如何解决此类问题。我的情况略有不同，因为 table2 的结果是动态的，并且列数可能小于 table1 的列数。但概念是一样的。
First, get the result of table2.
首先得到table2的结果。
Next, unpivot it.
接下来，取消旋转它。
Then write the update query using dynamic SQL. Sample code is written for testing 2 simple tables - tblA and tblB
然后使用动态 SQL 编写更新查询。示例代码用于测试 2 个简单的表 - tblA 和 tblB
--CREATE TABLE tblA(id int, col1 VARCHAR(25), col2 VARCHAR(25), col3 VARCHAR(25), col4 VARCHAR(25))
--CREATE TABLE tblB(id int, col1 VARCHAR(25), col2 VARCHAR(25), col3 VARCHAR(25), col4 VARCHAR(25))
--INSERT INTO tblA(id, col1, col2, col3, col4)
--VALUES(1,'A1','A2','A3','A4')
--INSERT INTO tblB(id, col1, col2, col3, col4)
--VALUES(1,'B1','B2','B3','B4')
DECLARE @id VARCHAR(10) = 1, @TSQL NVARCHAR(MAX)
DECLARE @tblPivot TABLE(
colName VARCHAR(255),
val VARCHAR(255)
)
INSERT INTO @tblPivot
SELECT colName, val
FROM tblB
UNPIVOT
(
val
FOR colName IN (col1, col2, col3, col4)
) unpiv
WHERE id = @id
SELECT @TSQL = COALESCE(@TSQL + '''
,','') + colName + ' = ''' + val
FROM @tblPivot
SET @TSQL = N'UPDATE tblA
SET ' + @TSQL + '''
WHERE id = ' + @id
PRINT @TSQL
--EXEC SP_EXECUTESQL @TSQL
PRINT @TSQLresult:
PRINT @TSQL结果：
