如何在 sql server 2008 中交换列值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4198587/
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
How do I swap column values in sql server 2008?
提问by jay
I have a table called Employee
我有一张表叫员工
Eno ename AttributeValue AttributeName
1 aa a123 abc
2 bbb b123 dcf
3 cc c7sd wew3
I want to swap the data from column AttributeValueto AttributeNameand AttributeNameto AttributeValue
我希望将数据从交换柱AttributeValue到AttributeName和AttributeName到AttributeValue
For Example:
例如：
Eno ename AttributeValue AttributeName
1 aa abc a123
2 bbb dcf b123
3 cc wew3 c7sd
回答by Oded
UPDATE employee
SET AttributeValue = AttributeName,
AttributeName = AttributeValue
However, unless both columns have the exact same definition, you risk losing information.
但是，除非两列具有完全相同的定义，否则您可能会丢失信息。
回答by John Hartsock
Update employee
Set attributeValue = attributeName,
attributeName = attributeValue
回答by GendoIkari
update Employee set AttributeValue = AttributeName, AttributeName = AttributeValue
update Employee set AttributeValue = AttributeName, AttributeName = AttributeValue
回答by Mind Peace
This is really good example
这真是个好例子
SELECT * from employees;
Go
DECLARE @temp as varchar(20)
update employees
set @temp = fname,
fname = lname,
lname = @temp
WHERE deptno = 10;
GO
SELECT * from employees;
回答by bormat
All the previous techniques are slow for big table they move date instead of rename columns, this is a simple solution:
所有以前的技术对于他们移动日期而不是重命名列的大表来说都很慢，这是一个简单的解决方案：
ALTER TABLE "amplitude"
RENAME COLUMN "start_hour_displayed" TO "temp";
ALTER TABLE "amplitude"
RENAME COLUMN "start_hour" TO "start_hour_displayed";
ALTER TABLE "amplitude"
RENAME COLUMN "temp" TO "start_hour";
If you have views of functions linked you have to backup them before and restore them after.
如果您有链接的功能视图，您必须先备份它们，然后再恢复它们。
回答by Arpit Trivedi
Declare @myTable Table (id int, first_name varchar(50), last_name varchar(50));
Select * from Student
Insert Into @myTable (id, first_name, last_name) Select id, last_name, first_name from Student
MERGE
INTO Student std
USING @myTable tmp
ON std.id = tmp.id
WHEN MATCHED THEN
UPDATE
SET std.first_name = tmp.first_name,
std.last_name = tmp.last_name;
Select * from Student
Output
输出
回答by Dinesh Kumar Sharma
Just swap both columns in a single update:
只需在一次更新中交换两列：
Update registration
Set AttributeName = AttributeValue ,
AttributeValue = AttributeName where id in (1,2,3)
回答by Tann
UPDATE employee SET AttributeValue = AttributeName, AttributeName = AttributeValue its not correct better to create one temp column and try the code UPDATE employee SET temp= AttributeName then again UPDATE employee SET AttributeName = AttributeValuee again
更新员工 SET AttributeValue = AttributeName, AttributeName = AttributeValue 创建一个临时列并尝试代码更新员工 SET temp= AttributeName 然后再次更新员工 SET AttributeName = AttributeValuee 是不正确的
UPDATE employee SET AttributeValuee= temp
更新员工 SET AttributeValuee= temp
