MySQL에서 여러 행의 여러 열을 다른 값으로 업데이트
이 기사에서는 CASE 문, IF() 함수, INSERT ... ON DUPLICATE KEY UPDATE 절 및 UPDATE와 JOIN() 함수를 사용하여 여러 열을 업데이트하는 방법을 배웁니다. MySQL에서 다른 값을 가진 여러 행에.
MySQL에서 여러 레코드(행)의 여러 열을 다른 값으로 업데이트
경우에 따라 데이터베이스의 서로 다른 값으로 여러 행의 여러 열을 업데이트해야 합니다. 테이블에 몇 개의 레코드가 있는 경우 여러 개의 UPDATE 문을 사용해도 좋습니다.
테이블에 수백만 개의 행이 있다고 가정합니다. 테이블을 업데이트하는 몇 가지 방법이 아래에 나열되어 있습니다.
CASE문을 사용하십시오.IF()기능을 사용하십시오.INSERT ... ON DUPLICATE KEY UPDATE를 사용합니다.UPDATE를JOIN()과 함께 사용합니다.
위에서 언급한 접근 방식을 배우려면 속성(열)으로 ID, JavaScore 및 PythonScore가 있는 students라는 테이블을 생성합니다. 여기서 ID는 기본 키입니다. 아래 쿼리를 사용하여 이 자습서를 따라 테이블을 만들고 채울 수 있습니다.
예제 코드:
# create a table
CREATE TABLE students(
ID INT NOT NULL,
JavaScore INT NOT NULL,
PythonScore INT NOT NULL,
PRIMARY KEY (ID));
# insert data
INSERT INTO students (ID, JavaScore, PythonScore)
VALUES
(1, 70, 65),
(2, 75, 80),
(3, 81, 89),
(4, 50, 70);
# display table data
SELECT * FROM students;
출력:
| ID | 자바스코어 | PythonScore |
|---|---|---|
| 1 | 70 | 65 |
| 2 | 75 | 80 |
| 삼 | 81 | 89 |
| 4 | 50 | 70 |
students 테이블이 생성되고 채워지면 언급된 접근 방식을 사용할 수 있습니다.
CASE 문 사용
예제 코드:
UPDATE students
SET JavaScore = (case
when ID = 1 then 75
when ID = 2 then 80
when ID = 3 then 86
when ID = 4 then 55
end),
PythonScore = (case
when ID = 1 then 70
when ID = 2 then 85
when ID = 3 then 94
when ID = 4 then 75
end)
WHERE ID in (1,2,3,4);
업데이트된 결과를 얻으려면 SELECT 문을 사용하십시오.
SELECT * FROM students;
출력:
| ID | 자바스코어 | PythonScore |
|---|---|---|
| 1 | 75 | 70 |
| 2 | 80 | 85 |
| 삼 | 86 | 94 |
| 4 | 55 | 75 |
모든 조건을 통과하고 첫 번째 조건이 충족되면 항목(값)을 출력하는 CASE 문을 사용하여 여러 행의 여러 열을 다른 값으로 업데이트합니다(예: if-then-else 문). 조건이 TRUE이면 읽기를 중지하고 해당 결과를 반환합니다.
TRUE 조건이 없다고 가정하면 ELSE 부분이 실행됩니다. ELSE 섹션이 없으면 NULL을 반환합니다.
모든 레코드에 대해 일정하게 유지하려는 DATETIME 유형의 다른 필드가 있는 경우 쿼리는 다음과 같습니다.
예제 코드:
UPDATE students
SET JavaScore = (case
when ID = 1 then 75
when ID = 2 then 80
when ID = 3 then 86
when ID = 4 then 55
end),
PythonScore = (case
when ID = 1 then 70
when ID = 2 then 85
when ID = 3 then 94
when ID = 4 then 75
end),
DATEANDTIME = NOW()
WHERE ID in (1,2,3,4);
IF() 함수 사용
예제 코드:
UPDATE students SET
JavaScore = IF(ID=1,76,IF(ID=2,81,IF(ID=3,87,IF(ID=4,56,NULL)))),
PythonScore = IF(ID=1,71,IF(ID=2,86,IF(ID=3,95,IF(ID=4,76,NULL))))
WHERE ID IN (1,2,3,4);
SELECT 명령을 실행하여 students 테이블의 새 값을 가져옵니다.
SELECT * FROM students;
출력:
| ID | 자바스코어 | PythonScore |
|---|---|---|
| 1 | 76 | 71 |
| 2 | 81 | 86 |
| 삼 | 87 | 95 |
| 4 | 56 | 76 |
조건이 만족되면 특정 값을 반환하는 IF() 함수를 사용합니다. 그렇지 않으면 지정된 다른 값을 반환합니다. 구문은 IF(조건, TrueValue, FalseValue)입니다.
ID=1 조건이 충족되면 질문이 있을 수 있습니다. 그러면 다른 IF()로 이동하는 이유는 무엇입니까? 다음과 같이 중첩된 IF() 함수를 사용하여 여러 개의 IF()를 만듭니다.
IF(condition, TrueValue,
IF(condition, TrueValue,
IF(condition, TrueValue,
IF(condition, TrueValue, FalseValue)
)
)
)
좀 더 이해하기 쉽게 해보자. 다음 스니펫에는 여러 IF가 있으며 조건이 충족되는지 여부는 중요하지 않습니다.
모든 IF 조건을 확인하고 그에 따라 값을 설정합니다. 마지막 IF에는 ELSE 부분이 있으며 네 번째 IF 조건이 FALSE인 경우에만 실행됩니다.
IF Condition
TrueValue
IF Condition
TrueValue
IF Condition
TrueValue
IF Condition
TrueValue
ELSE
FalseValue
중첩된 IF() 함수를 사용하는 이유는 다른 값으로 여러 행을 업데이트하기 위해서입니다.
여러 행의 여러 열을 업데이트해야 하는 경우 중첩된 IF() 함수보다 이해하고 관리하기 쉽기 때문에 CASE 문을 사용하는 것이 좋습니다.
INSERT ... ON DUPLICATE KEY UPDATE 사용
예제 코드:
INSERT INTO students (ID, JavaScore, PythonScore)
VALUES
(1, 77, 72),(2, 82, 87),(3, 88, 96),(4, 57, 77)
ON DUPLICATE KEY UPDATE
JavaScore = VALUES(JavaScore),
PythonScore = VALUES(PythonScore);
출력:
| ID | 자바스코어 | PythonScore |
|---|---|---|
| 1 | 77 | 72 |
| 2 | 82 | 87 |
| 삼 | 88 | 96 |
| 4 | 57 | 77 |
이 예는 INSERT ... ON DUPLICATE KEY UPDATE를 보여줍니다. 일반적으로 PRIMARY KEY 또는 UNIQUE 인덱스에서 중복을 유발할 수 있는 특정 테이블에 INSERT하면 오류가 발생합니다.
그러나 ON DUPLICATE KEY UPDATE를 지정하면 MySQL은 최신 값으로 기존 레코드를 업데이트합니다. PRIMARY KEY에서 중복 항목이 발견되면 해당 특정 열의 값이 현재 값으로 설정됩니다.
이 자습서를 작성할 때 VALUES() 함수가 작동하지만 VALUES() 함수가 더 이상 사용되지 않으며 향후 릴리스에서 제거될 것이라는 경고가 표시됩니다. 추가 지원을 위해 MySQL 설명서를 고려할 수 있습니다.
UPDATE를 JOIN()과 함께 사용
예제 코드:
UPDATE students std
JOIN (
SELECT 1 AS ID, 78 AS JavaScore, 73 AS PythonScore
UNION ALL
SELECT 2 AS ID, 83 AS JavaScore, 88 AS PythonScore
UNION ALL
SELECT 3 AS ID, 89 AS JavaScore, 97 AS PythonScore
UNION ALL
SELECT 4 AS ID, 58 AS JavaScore, 78 AS PythonScore
) temp
ON std.ID = temp.ID
SET std.JavaScore = temp.JavaScore, std.PythonScore = temp.PythonScore;
이 솔루션은 안전 모드가 비활성화된 경우에만 작동합니다. Edit->Preference->SQL Editor로 이동하고 Safe Mode 옵션을 선택 취소하여 MySQL Workbench에서 비활성화할 수 있습니다.
그런 다음 MySQL 서버를 다시 시작하고 위에 제공된 쿼리를 실행하고 SELECT * FROM students;를 사용합니다. 명령을 사용하여 다음 결과를 얻습니다.
출력:
| ID | 자바스코어 | PythonScore |
|---|---|---|
| 1 | 78 | 73 |
| 2 | 83 | 88 |
| 삼 | 89 | 97 |
| 4 | 58 | 78 |
SELECT 및 UNION ALL을 사용하여 JOIN() 내부에서 데이터를 수집합니다. 완료되면 JOIN()을 사용하여 모든 데이터를 조인하고 ID 속성에 대해 만족하는 모든 조건에서 JavaScore 및 PythonScore를 설정합니다.
