MySQL에서 열 값 교환
좌표가있는 MySQL 테이블이 있고 열 이름은 X와 Y입니다. 이제 X에서 Y가되고 Y가 X가되도록이 테이블의 열 값을 바꾸고 싶습니다. 가장 확실한 해결책은 열의 이름을 바꾸는 것이지만 내가 할 수있는 권한이 없기 때문에 구조를 변경하고 싶지 않습니다.
이것은 어떤 방식 으로 UPDATE 와 관련이 있습니까? 업데이트 테이블 SET X = Y, Y = X는 분명히 내가 원하는 것을하지 않습니다.
편집 : 위에서 언급 한 권한에 대한 제한으로 인해 ALTER TABLE 또는 테이블 / 데이터베이스 구조를 변경하는 다른 명령을 효과적으로 사용할 수 없습니다. 열 이름을 바꾸거나 새 열을 추가하는 것은 불행히도 옵션이 아닙니다.
방금 똑같은 문제를 해결해야했으며 내 연구 결과를 요약하겠습니다.
이
UPDATE table SET X=Y, Y=X방법은 두 값을 모두 Y로 설정하기 때문에 분명히 작동하지 않습니다.임시 변수를 사용하는 방법은 다음과 같습니다. "IS NOT NULL"조정에 대한 http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/ 의 의견에서 Antony에게 감사드립니다 . 이것이 없으면 쿼리가 예기치 않게 작동합니다. 게시물 끝에있는 테이블 스키마를 참조하십시오. 이 방법은 값 중 하나가 NULL 인 경우 값을 교환하지 않습니다. 이 제한이없는 방법 # 3을 사용하십시오.
UPDATE swap_test SET x=y, y=@temp WHERE (@temp:=x) IS NOT NULL;이 방법은 Dipin에서 http://beerpla.net/2009/02/17/swapping-column-values-in-mysql/에 대한 의견으로 제공되었습니다 . 가장 우아하고 깨끗한 솔루션이라고 생각합니다. NULL 값과 NULL이 아닌 값 모두에서 작동합니다.
UPDATE swap_test SET x=(@temp:=x), x = y, y = @temp;내가 생각해 낸 또 다른 접근법은 효과가있는 것 같습니다.
UPDATE swap_test s1, swap_test s2 SET s1.x=s1.y, s1.y=s2.x WHERE s1.id=s2.id;
기본적으로 첫 번째 테이블은 업데이트되는 테이블이고 두 번째 테이블은 이전 데이터를 가져 오는 데 사용됩니다.
이 방법을 사용하려면 기본 키가 있어야합니다.
이것은 내 테스트 스키마입니다.
CREATE TABLE `swap_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`x` varchar(255) DEFAULT NULL,
`y` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `swap_test` VALUES ('1', 'a', '10');
INSERT INTO `swap_test` VALUES ('2', NULL, '20');
INSERT INTO `swap_test` VALUES ('3', 'c', NULL);
X와 Y를 사용하여 합을 취하고 반대 값을 뺄 수 있습니다.
UPDATE swaptest SET X=X+Y,Y=X-Y,X=X-Y;
다음은 샘플 테스트입니다 (음수와 함께 작동).
mysql> use test
Database changed
mysql> drop table if exists swaptest;
Query OK, 0 rows affected (0.03 sec)
mysql> create table swaptest (X int,Y int);
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO swaptest VALUES (1,2),(3,4),(-5,-8),(-13,27);
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM swaptest;
+------+------+
| X | Y |
+------+------+
| 1 | 2 |
| 3 | 4 |
| -5 | -8 |
| -13 | 27 |
+------+------+
4 rows in set (0.00 sec)
mysql>
스왑이 수행되고 있습니다.
mysql> UPDATE swaptest SET X=X+Y,Y=X-Y,X=X-Y;
Query OK, 4 rows affected (0.07 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> SELECT * FROM swaptest;
+------+------+
| X | Y |
+------+------+
| 2 | 1 |
| 4 | 3 |
| -8 | -5 |
| 27 | -13 |
+------+------+
4 rows in set (0.00 sec)
mysql>
시도 해봐 !!!
다음 코드는 빠른 테스트의 모든 시나리오에서 작동합니다.
UPDATE table swap_test
SET x=(@temp:=x), x = y, y = @temp
업데이트 테이블 SET X = Y, Y = X 는 원하는대로 정확하게 수행합니다 (편집 : MySQL이 아닌 PostgreSQL에서 아래 참조). 이전 행에서 값을 가져와 같은 행의 새 사본에 할당 한 다음 이전 행을 바꿉니다. 임시 테이블, 임시 열 또는 다른 스왑 트릭을 사용하지 않아도됩니다.
@ D4V360 : 알겠습니다. 충격적이고 예상치 못한 일입니다. PostgreSQL을 사용하고 답변이 올바르게 작동합니다 (시도했습니다). SET 절의 오른쪽에있는 표현식이 명시 적으로 이전 열 값을 사용한다고 언급하는 PostgreSQL UPDATE 문서 (매개 변수, 표현식 아래)를 참조하십시오 . 해당 MySQL UPDATE 문서에 "단일 테이블 UPDATE 할당이 일반적으로 왼쪽에서 오른쪽으로 평가됩니다"라는 문장이 포함되어 있음을 알았습니다 .
알아 둘만 한.
좋아, 그냥 재미로, 당신은 이것을 할 수 있습니다! (문자열 값을 교환한다고 가정)
mysql> select * from swapper;
+------+------+
| foo | bar |
+------+------+
| 6 | 1 |
| 5 | 2 |
| 4 | 3 |
+------+------+
3 rows in set (0.00 sec)
mysql> update swapper set
-> foo = concat(foo, "###", bar),
-> bar = replace(foo, concat("###", bar), ""),
-> foo = replace(foo, concat(bar, "###"), "");
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from swapper;
+------+------+
| foo | bar |
+------+------+
| 1 | 6 |
| 2 | 5 |
| 3 | 4 |
+------+------+
3 rows in set (0.00 sec)
MySQL에서 왼쪽에서 오른쪽으로 평가 프로세스를 남용하는 것은 재미있다.
또는 숫자 인 경우 XOR을 사용하십시오. 좌표를 언급 했으므로 멋진 정수 값이나 복잡한 문자열이 있습니까?
편집 : XOR 물건은 다음과 같이 작동합니다.
update swapper set foo = foo ^ bar, bar = foo ^ bar, foo = foo ^ bar;
두 가지 대안 1. 임시 테이블 사용 2. XOR 알고리즘 조사
ALTER TABLE table ADD COLUMN tmp;
UPDATE table SET tmp = X;
UPDATE table SET X = Y;
UPDATE table SET Y = tmp;
ALTER TABLE table DROP COLUMN tmp;
편집 : 그렉의 의견에 관하여 : 아니오, 작동하지 않습니다 :
mysql> select * from test; +------+------+ | x | y | +------+------+ | 1 | 2 | | 3 | 4 | +------+------+ 2 rows in set (0.00 sec)mysql> update test set x=y, y=x; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from test; +------+------+ | x | y | +------+------+ | 2 | 2 | | 4 | 4 | +------+------+ 2 rows in set (0.00 sec)
다음과 같은 방법으로 중간 교환 변수가 가장 좋습니다.
update z set c1 = @c := c1, c1 = c2, c2 = @c
첫째, 항상 작동합니다. 둘째, 데이터 유형에 관계없이 작동합니다.
둘 다에도 불구하고
update z set c1 = c1 ^ c2, c2 = c1 ^ c2, c1 = c1 ^ c2
과
update z set c1 = c1 + c2, c2 = c1 - c2, c1 = c1 - c2
일반적으로 숫자 데이터 형식에 대해서만 작동하고 오버플로를 방지하는 것은 사용자의 책임입니다. 부호가있는 것과 부호가없는 사이에 XOR을 사용할 수 없으며 오버플로 가능성에 대해 합계를 사용할 수 없습니다.
과
update z set c1 = c2, c2 = @c where @c := c1
c1이 0 또는 NULL이거나 길이가 0 인 문자열이거나 공백 일 경우 작동하지 않습니다.
우리는 그것을 바꿔야한다
update z set c1 = c2, c2 = @c where if((@c := c1), true, true)
스크립트는 다음과 같습니다.
mysql> create table z (c1 int, c2 int)
-> ;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into z values(0, 1), (-1, 1), (pow(2, 31) - 1, pow(2, 31) - 2)
-> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 0 | 1 |
| -1 | 1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.02 sec)
mysql> update z set c1 = c1 ^ c2, c2 = c1 ^ c2, c1 = c1 ^ c2;
ERROR 1264 (22003): Out of range value for column 'c1' at row 2
mysql> update z set c1 = c1 + c2, c2 = c1 - c2, c1 = c1 - c2;
ERROR 1264 (22003): Out of range value for column 'c1' at row 3
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 0 | 1 |
| 1 | -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.02 sec)
mysql> update z set c1 = c2, c2 = @c where @c := c1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 0 | 1 |
| -1 | 1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.00 sec)
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 1 | 0 |
| 1 | -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.00 sec)
mysql> update z set c1 = @c := c1, c1 = c2, c2 = @c;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 0 | 1 |
| -1 | 1 |
| 2147483647 | 2147483646 |
+------------+------------+
3 rows in set (0.00 sec)
mysql>update z set c1 = c2, c2 = @c where if((@c := c1), true, true);
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from z;
+------------+------------+
| c1 | c2 |
+------------+------------+
| 1 | 0 |
| 1 | -1 |
| 2147483646 | 2147483647 |
+------------+------------+
3 rows in set (0.00 sec)
이것은 확실히 작동합니다! 나는 유로와 SKK 가격 열을 교환하기 위해 방금 필요했습니다. :)
UPDATE tbl SET X=Y, Y=@temp where @temp:=X;
The above will not work (ERROR 1064 (42000): You have an error in your SQL syntax)
Assuming you have signed integers in your columns, you may need to use CAST(a ^ b AS SIGNED), since the result of the ^ operator is an unsigned 64-bit integer in MySQL.
In case it helps anyone, here's the method I used to swap the same column between two given rows:
SELECT BIT_XOR(foo) FROM table WHERE key = $1 OR key = $2
UPDATE table SET foo = CAST(foo ^ $3 AS SIGNED) WHERE key = $1 OR key = $2
where $1 and $2 are the keys of two rows and $3 is the result of the first query.
I've not tried it but
UPDATE tbl SET @temp=X, X=Y, Y=@temp
Might do it.
Mark
You could change column names, but this is more of a hack. But be cautious of any indexes that may be on these columns
Swapping of column values using single query
UPDATE my_table SET a=@tmp:=a, a=b, b=@tmp;
cheers...!
I had to just move value from one column to the other (like archiving) and reset the value of the original column.
The below (reference of #3 from accepted answer above) worked for me.
Update MyTable set X= (@temp:= X), X = 0, Y = @temp WHERE ID= 999;
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;
This example swaps start_date and end_date for records where the dates are the wrong way round (when performing ETL into a major rewrite, I found some start dates later than their end dates. Down, bad programmers!).
In situ, I'm using MEDIUMINTs for performance reasons (like Julian days, but having a 0 root of 1900-01-01), so I was OK doing a condition of WHERE mdu.start_date > mdu.end_date.
The PKs were on all 3 columns individually (for operational / indexing reasons).
UPDATE monitor_date mdu
INNER JOIN monitor_date mdc
ON mdu.register_id = mdc.register_id
AND mdu.start_date = mdc.start_date
AND mdu.end_date = mdc.end_date
SET mdu.start_date = mdu.end_date, mdu.end_date = mdc.start_date
WHERE mdu.start_date > mdu.end_date;
Table name is customer. fields are a and b, swap a value to b;.
UPDATE customer SET a=(@temp:=a), a = b, b = @temp
I checked this is working fine.
In SQL Server, you can use this query:
update swaptable
set col1 = t2.col2,
col2 = t2.col1
from swaptable t2
where id = t2.id
Let's say you want to swap the value of first and last name in tb_user.
The safest would be:
- Copy tb_user. So you will have 2 tables: tb_user and tb_user_copy
- Use UPDATE INNER JOIN query
UPDATE tb_user a
INNER JOIN tb_user_copy b
ON a.id = b.id
SET a.first_name = b.last_name, a.last_name = b.first_name
참고URL : https://stackoverflow.com/questions/37649/swapping-column-values-in-mysql
'Programing' 카테고리의 다른 글
| 회사 이름은 어디에 설정합니까? (0) | 2020.07.15 |
|---|---|
| IBus 문제 해결-1.5.11 이전의 IBus는 입력 문제를 일으킬 수 있습니다 (0) | 2020.07.15 |
| SoapClient 클래스를 사용하여 PHP SOAP 호출을 만드는 방법 (0) | 2020.07.15 |
| 스칼라 : 닐 vs리스트 () (0) | 2020.07.15 |
| 문자열을 소문자로 변환하는 MySQL 명령이 있습니까? (0) | 2020.07.15 |