15.2.17 UPDATE Statement

15.2.17 UPDATE Statement

UPDATE는 테이블의 행을 수정하는 DML 문입니다.

UPDATE 문은 공통 테이블 식을 정의하기 위해 WITH 절로 시작할 수 있으며, 이렇게 정의된 공통 테이블 식은 해당 UPDATE 내에서 접근할 수 있습니다. 자세한 내용은 Section 15.2.20, “WITH (Common Table Expressions)”을 참고하십시오.

Single-table 문법:

1UPDATE [LOW_PRIORITY] [IGNORE] table_reference
2    SET assignment_list
3    [WHERE where_condition]
4    [ORDER BY ...]
5    [LIMIT row_count]
6
7value:
8    {expr | DEFAULT}
9
10assignment:
11    col_name = value
12
13assignment_list:
14    assignment [, assignment] ...

Multiple-table 문법:

1UPDATE [LOW_PRIORITY] [IGNORE] table_references
2    SET assignment_list
3    [WHERE where_condition]

Single-table 문법의 경우, UPDATE 문은 지정된 테이블의 기존 행의 컬럼들을 새로운 값으로 업데이트합니다. SET 절은 어떤 컬럼을 수정할지와 그 컬럼에 부여할 값을 지정합니다. 각 값은 표현식으로 지정하거나, 키워드 DEFAULT를 사용하여 컬럼을 명시적으로 기본값으로 설정할 수 있습니다.

WHERE 절이 주어지면, 어떤 행을 업데이트할지 식별하는 조건을 지정합니다. WHERE 절이 없으면 모든 행이 업데이트됩니다. ORDER BY 절이 지정되면, 지정된 순서대로 행이 업데이트됩니다. LIMIT 절은 업데이트할 수 있는 행의 개수에 제한을 둡니다.

Multiple-table 문법의 경우, UPDATE는 table_references 에 명시된 각 테이블에서 조건을 만족하는 행을 업데이트합니다. 각 매칭되는 행은 조건을 여러 번 만족하더라도 한 번만 업데이트됩니다. Multiple-table 문법에서는 ORDER BY와 LIMIT을 사용할 수 없습니다.

파티션된 테이블의 경우, 이 문장의 single-table 및 multiple-table 양식 모두에서 테이블 참조의 일부로 PARTITION 절 사용을 지원합니다. 이 옵션은 하나 이상의 파티션 또는 서브파티션 (또는 둘 다)의 목록을 사용합니다. 나열된 파티션 (또는 서브파티션)만이 매치 여부를 검사하며, 이러한 파티션 또는 서브파티션에 속하지 않는 행은 where_condition 을 만족하더라도 업데이트되지 않습니다.

참고

INSERT 또는 REPLACE 문에서 PARTITION을 사용할 때와는 달리, 나머지 부분이 유효한 UPDATE ... PARTITION 문은 나열된 파티션 (또는 서브파티션)에서 어떤 행도 where_condition 을 만족하지 않더라도 성공한 것으로 간주됩니다.

자세한 정보와 예제는 Section 26.5, “Partition Selection”을 참고하십시오.

where_condition 은 업데이트할 각 행에 대해 true로 평가되는 표현식입니다. 표현식 문법에 대해서는 Section 11.5, “Expressions”을 참고하십시오.

table_references 와 where_condition 은 Section 15.2.13, “SELECT Statement”에 설명된 대로 지정합니다.

UPDATE 권한은 실제로 업데이트되는 UPDATE에 참조된 컬럼에 대해서만 필요합니다. 읽기만 하고 수정하지 않는 컬럼에 대해서는 SELECT 권한만 필요합니다.

UPDATE 문은 다음 수식어를 지원합니다:

• LOW_PRIORITY 수식어를 사용하면, 테이블에서 다른 클라이언트가 읽기를 수행하지 않을 때까지 UPDATE 실행이 지연됩니다. 이는 MyISAM, MEMORY, MERGE와 같이 테이블 수준 잠금만 사용하는 스토리지 엔진에만 영향을 미칩니다.

• IGNORE 수식어를 사용하면, 업데이트 중 오류가 발생해도 업데이트 문은 중단되지 않습니다. 유니크 키 값에서 중복 키 충돌이 발생하는 행은 업데이트되지 않습니다. 데이터 변환 오류를 유발하는 값으로 업데이트되는 행은 대신 가장 가까운 유효한 값으로 업데이트됩니다. 자세한 내용은 The Effect of IGNORE on Statement Execution을 참고하십시오.

UPDATE IGNORE 문은 ORDER BY 절을 포함한 경우를 포함하여 문 기반 복제에서 unsafe로 표시됩니다. (이는 행이 업데이트되는 순서가 어떤 행이 무시되는지를 결정하기 때문입니다.) 이러한 문은 문 기반 모드를 사용할 때 오류 로그에 경고를 남기며, MIXED 모드를 사용하는 경우 행 기반 포맷을 사용하여 바이너리 로그에 기록됩니다. (Bug #11758262, Bug #50439) 자세한 내용은 Section 19.2.1.3, “Determination of Safe and Unsafe Statements in Binary Logging”을 참고하십시오.

업데이트할 테이블의 컬럼을 표현식에서 참조하면, UPDATE는 해당 컬럼의 현재 값을 사용합니다. 예를 들어, 다음 문은 col1을 현재 값보다 1만큼 크게 설정합니다:

1UPDATE t1 SET col1 = col1 + 1;

다음 문에서 두 번째 할당은 col2를 원래의 col1 값이 아니라 현재 (업데이트된) col1 값으로 설정합니다. 그 결과 col1과 col2는 동일한 값을 갖게 됩니다. 이 동작은 표준 SQL과 다릅니다.

1UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE 할당은 일반적으로 왼쪽에서 오른쪽으로 평가됩니다. Multiple-table 업데이트의 경우, 할당이 어떤 특정 순서로 수행된다는 보장은 없습니다.

어떤 컬럼을 현재 가지고 있는 값으로 설정하면, MySQL은 이를 감지하고 해당 컬럼을 업데이트하지 않습니다.

NOT NULL로 선언된 컬럼을 NULL로 업데이트하면, strict SQL 모드가 활성화된 경우 오류가 발생합니다. 그렇지 않으면 컬럼은 해당 컬럼 데이터 타입의 암시적 기본값으로 설정되고 경고 개수가 증가합니다. 암시적 기본값은 숫자 타입의 경우 0, 문자열 타입의 경우 빈 문자열 (''), 날짜와 시간 타입의 경우 “zero” 값입니다. Section 13.6, “Data Type Default Values”을 참고하십시오.

Generated 컬럼을 명시적으로 업데이트하는 경우 허용되는 유일한 값은 DEFAULT입니다. Generated 컬럼에 대한 정보는 Section 15.1.24.8, “CREATE TABLE and Generated Columns”을 참고하십시오.

UPDATE는 실제로 변경된 행의 개수를 반환합니다. mysql_info() C API 함수는 매치되어 업데이트된 행의 개수와 UPDATE 중 발생한 경고의 개수를 반환합니다.

LIMIT row_count를 사용하여 UPDATE의 적용 범위를 제한할 수 있습니다. LIMIT 절은 rows-matched 제한입니다. 이 문은 실제로 변경되었는지 여부와 관계없이 WHERE 절을 만족하는 row_count 개의 행을 찾는 즉시 중지됩니다.

UPDATE 문에 ORDER BY 절이 포함되면, 행은 해당 절에 지정된 순서대로 업데이트됩니다. 이는 그렇지 않으면 오류가 발생할 수 있는 특정 상황에서 유용할 수 있습니다. 예를 들어, 테이블 t에 유니크 인덱스가 설정된 컬럼 id가 있다고 가정하고, 다음 문은 행이 업데이트되는 순서에 따라 중복 키 오류로 실패할 수 있습니다:

1UPDATE t SET id = id + 1;

예를 들어, 테이블의 id 컬럼에 1과 2가 있고, 2가 3으로 업데이트되기 전에 1이 2로 업데이트되면 오류가 발생합니다. 이 문제를 피하려면 id 값이 큰 행이 작은 행보다 먼저 업데이트되도록 ORDER BY 절을 추가합니다:

1UPDATE t SET id = id + 1 ORDER BY id DESC;

여러 테이블에 걸친 UPDATE 작업을 수행할 수도 있습니다. 그러나 multiple-table UPDATE에서는 ORDER BY나 LIMIT을 사용할 수 없습니다. table_references 절은 조인에 포함되는 테이블들을 나열합니다. 그 문법은 Section 15.2.13.2, “JOIN Clause”에 설명되어 있습니다. 다음은 예제입니다:

1UPDATE items,month SET items.price=month.price
2WHERE items.id=month.id;

위 예제는 콤마 연산자를 사용하는 inner join을 보여주지만, multiple-table UPDATE 문은 LEFT JOIN과 같이 SELECT 문에서 허용되는 어떤 종류의 조인도 사용할 수 있습니다.

Foreign key 제약 조건이 있는 InnoDB 테이블을 포함하는 multiple-table UPDATE 문을 사용하는 경우, MySQL 옵티마이저는 parent/child 관계의 순서와는 다른 순서로 테이블을 처리할 수 있습니다. 이 경우 문은 실패하고 롤백됩니다. 대신 단일 테이블을 업데이트하고, InnoDB가 제공하는 ON UPDATE 기능에 의존하여 다른 테이블이 적절히 수정되도록 해야 합니다. Section 15.1.24.5, “FOREIGN KEY Constraints”를 참고하십시오.

서브쿼리에서 동일한 테이블을 직접 선택하면서 동시에 해당 테이블을 업데이트할 수는 없습니다. 이를 우회하는 방법은, 실제로 업데이트하려는 테이블로부터 파생된 테이블 중 하나를 사용하는 multi-table 업데이트를 사용하고, 바깥쪽 WHERE 절에서 별칭을 사용해 그 파생 테이블을 참조하는 것입니다. 예를 들어, 다음 문으로 정의된 items라는 이름의 테이블을 업데이트하려 한다고 가정합니다:

1CREATE TABLE items (
2    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
4    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
5    quantity BIGINT NOT NULL DEFAULT 0
6);

마진이 30% 이상이고 재고가 100개 미만인 item의 소매가를 인하하려고 할 때, 다음과 같이 WHERE 절의 서브쿼리를 사용하는 UPDATE 문을 시도할 수 있습니다. 그러나 여기에서 보듯이 이 문은 동작하지 않습니다:

1mysql> UPDATE items
2     > SET retail = retail * 0.9
3     > WHERE id IN
4     >     (SELECT id FROM items
5     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
6ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

대신, 서브쿼리를 업데이트할 테이블 목록으로 이동시키고 별칭을 사용해 바깥쪽 WHERE 절에서 이를 참조하는 multi-table 업데이트를 사용할 수 있습니다. 다음과 같습니다:

1UPDATE items,
2       (SELECT id FROM items
3        WHERE id IN
4            (SELECT id FROM items
5             WHERE retail / wholesale >= 1.3 AND quantity < 100))
6        AS discounted
7SET items.retail = items.retail * 0.9
8WHERE items.id = discounted.id;

옵티마이저는 기본적으로 파생 테이블 discounted를 가장 바깥쪽 쿼리 블록에 머지하려고 하기 때문에, 이 방법은 파생 테이블의 materialization을 강제하는 경우에만 동작합니다. 이를 위해 업데이트를 실행하기 전에 optimizer_switch 시스템 변수의 derived_merge 플래그를 off로 설정하거나, 다음과 같이 NO_MERGE 옵티마이저 힌트를 사용할 수 있습니다:

1UPDATE /*+ NO_MERGE(discounted) */ items,
2       (SELECT id FROM items
3        WHERE retail / wholesale >= 1.3 AND quantity < 100)
4        AS discounted
5    SET items.retail = items.retail * 0.9
6    WHERE items.id = discounted.id;

이 경우 옵티마이저 힌트를 사용하는 장점은, 힌트가 사용된 쿼리 블록 내에서만 적용되므로, UPDATE를 실행한 후에 다시 optimizer_switch 값을 변경할 필요가 없다는 점입니다.

또 다른 방법으로는, 서브쿼리를 IN이나 EXISTS를 사용하지 않도록 다시 작성하는 것입니다. 다음과 같습니다:

1UPDATE items,
2       (SELECT id, retail / wholesale AS markup, quantity FROM items)
3       AS discounted
4    SET items.retail = items.retail * 0.9
5    WHERE discounted.markup >= 1.3
6    AND discounted.quantity < 100
7    AND items.id = discounted.id;

이 경우 서브쿼리는 머지되지 않고 기본적으로 materialize되므로, 파생 테이블의 머지를 비활성화할 필요가 없습니다.