在 Oracle 中交换列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21827804/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-19 02:14:06 来源:igfitidea点击:
Swapping column values in Oracle
提问by Nikhil Joshi
I was solving one of the puzzles and came across swapping column values using DML queries:
我正在解决其中一个难题，并遇到了使用 DML 查询交换列值的问题：
SELECT * FROM TEMP_TABLE;
ID1, ID2
--------
20, 15
20, 15
20, 15
Solution is mathematical calculation:
解决方案是数学计算：
UPDATE TEMP_TABLE SET ID1=ID1+ID2;
UPDATE TEMP_TABLE SET ID2=ID1-ID2;
UPDATE TEMP_TABLE SET ID1=ID1-ID2;
Now, I am trying to figure out whether this can be applied to Strings or not, please suggest.
现在，我想弄清楚这是否可以应用于字符串，请提出建议。
SELECT * FROM TEMP_TABLE_NEW;
ID1, ID2
--------
ABC, XYZ
ABC, XYZ
ABC, XYZ
回答by René Nyffenegger
There's no need to have three update statements, one is sufficient:
没有必要有三个更新语句，一个就足够了：
UPDATE temp_table_new
SET id1 = id2,
id2 = id1;
回答by Ashutosh SIngh
CREATE TABLE Names
(
F_NAME VARCHAR(22),
L_NAME VARCHAR(22)
);
INSERT INTO Names VALUES('Ashutosh', 'Singh'),('Anshuman','Singh'),('Manu', 'Singh');
UPDATE Names N1 , Names N2 SET N1.F_NAME = N2.L_NAME , N1.L_NAME = N2.F_NAME
WHERE N1.F_NAME = N2.F_NAME;
SELECT * FROM Names;
回答by sharma
Before:
前：
select * from employ;
EMPNO FNAME LNAME
----- ---------- ----------
1001 kiran kumar
1002 santosh reddy
update employ e set fname=(select lname from employ where empno=e.empno),
lname=(select fname from employ where empno=e.empno);
After:
后：
select * from employ;
EMPNO FNAME LNAME
------ ---------- ----------
1001 kumar kiran
1002 reddy santosh
