1. 개요

update-stmt:

WITH RECURSIVE common-table-expression , UPDATE OR ROLLBACK qualified-table-name OR REPLACE OR IGNORE OR FAIL OR ABORT SET column-name-list = expr column-name , FROM table-or-subquery , join-clause WHERE expr returning-clause

column-name-list:

( column-name ) ,

common-table-expression:

table-name ( column-name ) AS NOT MATERIALIZED ( select-stmt ) ,

select-stmt:

WITH RECURSIVE common-table-expression , SELECT DISTINCT result-column , ALL FROM table-or-subquery join-clause , WHERE expr GROUP BY expr HAVING expr , WINDOW window-name AS window-defn , VALUES ( expr ) , , compound-operator select-core ORDER BY LIMIT expr ordering-term , OFFSET expr , expr

compound-operator:

UNION UNION INTERSECT EXCEPT ALL

ordering-term:

expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST

result-column:

expr AS column-alias * table-name . *

window-defn:

( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )

frame-spec:

GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING RANGE ROWS UNBOUNDED PRECEDING expr PRECEDING CURRENT ROW expr PRECEDING CURRENT ROW expr FOLLOWING expr PRECEDING CURRENT ROW expr FOLLOWING EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERS

expr:

literal-value bind-parameter schema-name . table-name . column-name unary-operator expr expr binary-operator expr function-name ( function-arguments ) filter-clause over-clause ( expr ) , CAST ( expr AS type-name ) expr COLLATE collation-name expr NOT LIKE GLOB REGEXP MATCH expr expr ESCAPE expr expr ISNULL NOTNULL NOT NULL expr IS NOT DISTINCT FROM expr expr NOT BETWEEN expr AND expr expr NOT IN ( select-stmt ) expr , schema-name . table-function ( expr ) table-name , NOT EXISTS ( select-stmt ) CASE expr WHEN expr THEN expr ELSE expr END raise-function

filter-clause:

FILTER ( WHERE expr )

function-arguments:

DISTINCT expr , * ORDER BY ordering-term ,

ordering-term:

expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST

literal-value:

CURRENT_TIMESTAMP numeric-literal string-literal blob-literal NULL TRUE FALSE CURRENT_TIME CURRENT_DATE

over-clause:

OVER window-name ( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )

frame-spec:

ordering-term:

expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST

raise-function:

RAISE ( ROLLBACK , expr ) IGNORE ABORT FAIL

select-stmt:

compound-operator:

UNION UNION INTERSECT EXCEPT ALL

ordering-term:

expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST

result-column:

expr AS column-alias * table-name . *

window-defn:

( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )

frame-spec:

type-name:

name ( signed-number , signed-number ) ( signed-number )

signed-number:

+ numeric-literal -

join-clause:

table-or-subquery join-operator table-or-subquery join-constraint

join-constraint:

USING ( column-name ) , ON expr

join-operator:

NATURAL LEFT OUTER JOIN , RIGHT FULL INNER CROSS

qualified-table-name:

schema-name . table-name AS alias INDEXED BY index-name NOT INDEXED

returning-clause:

RETURNING expr AS column-alias * ,

table-or-subquery:

schema-name . table-name AS table-alias INDEXED BY index-name NOT INDEXED table-function-name ( expr ) , AS table-alias ( select-stmt ) ( table-or-subquery ) , join-clause

select-stmt:

compound-operator:

UNION UNION INTERSECT EXCEPT ALL

ordering-term:

expr COLLATE collation-name DESC ASC NULLS FIRST NULLS LAST

result-column:

expr AS column-alias * table-name . *

window-defn:

( base-window-name PARTITION BY expr , ORDER BY ordering-term , frame-spec )

frame-spec:

UPDATE 문은 UPDATE 문의 일부로 지정된 qualified-table-name(정규화된 테이블 이름)이 식별하는 데이터베이스 테이블의 0개 이상의 행에 저장된 값의 일부를 수정하는 데 사용해요.

2. 상세

UPDATE 문에 WHERE 절이 없으면 테이블의 모든 행이 UPDATE의 적용을 받아요. 그렇지 않으면 WHERE 절의 불리언 표현식이 참인 행만 UPDATE의 영향을 받아요. WHERE 절이 테이블의 어떤 행에 대해서도 참으로 평가되지 않아도 오류는 아니에요. 그저 UPDATE 문이 영향을 주는 행이 0개라는 뜻이에요.

UPDATE 문이 각 행에 적용하는 수정 내용은 SET 키워드 뒤에 오는 할당 목록에 따라 결정돼요. 각 할당은 등호 왼쪽에 column-name, 오른쪽에 스칼라 표현식을 지정해요. 영향을 받는 각 행에 대해 지정된 열은 해당 스칼라 표현식을 평가해서 얻은 값으로 설정돼요. 할당 표현식 목록에 같은 column-name이 두 번 이상 나타나면 가장 오른쪽 항목을 제외한 나머지는 무시돼요. 할당 목록에 없는 열은 수정되지 않은 채 남아요. 스칼라 표현식은 갱신 중인 행의 열을 참조할 수 있어요. 이 경우 모든 스칼라 표현식은 어떤 할당도 실행되기 전에 먼저 평가돼요.

SQLite 3.15.0(2016-10-14)부터 SET 절의 할당은 왼쪽에 괄호로 묶인 열 이름 목록을, 오른쪽에 같은 크기의 행 값을 가질 수 있어요.

UPDATE 키워드 뒤에 오는 선택적 "OR action" 충돌 절을 사용하면 사용자가 이 한 번의 UPDATE 명령 동안 사용할 특정 제약 조건 충돌 해결 알고리즘을 지정할 수 있어요. 자세한 내용은 ON CONFLICT 섹션을 참조하세요.

2.1. CREATE TRIGGER 내부의 UPDATE 문 제한 사항

다음의 추가 구문 제한은 CREATE TRIGGER 문 본문에 포함된 UPDATE 문에 적용돼요.

트리거 본문의 UPDATE 문에 지정된 table-name은 정규화(qualified)되지 않아야 해요. 즉, UPDATE 테이블 이름에 schema-name. 접두사를 붙일 수 없어요. 트리거가 연결된 테이블이 TEMP 데이터베이스에 있지 않다면, 트리거 프로그램이 갱신하는 테이블은 트리거가 연결된 테이블과 같은 데이터베이스에 있어야 해요. 트리거가 연결된 테이블이 TEMP 데이터베이스에 있다면, 갱신되는 테이블의 정규화되지 않은 이름은 최상위 문과 같은 방식으로 해석돼요. 즉, 먼저 TEMP 데이터베이스, 그다음 기본 데이터베이스, 그다음 연결된 순서대로 다른 데이터베이스들을 검색해요.
트리거 내의 UPDATE 문에는 INDEXED BY 및 NOT INDEXED 절을 사용할 수 없어요.
SQLite를 빌드할 때 사용한 컴파일 옵션과 관계없이, 트리거 내부에서는 UPDATE의 LIMIT 및 ORDER BY 절이 지원되지 않아요.

2.2. UPDATE FROM

UPDATE-FROM 개념은 UPDATE 문이 데이터베이스의 다른 테이블에 의해 주도되도록 허용하는 SQL 확장이에요. "대상(target)" 테이블은 갱신되는 특정 테이블이에요. UPDATE-FROM을 사용하면 대상 테이블을 데이터베이스의 다른 테이블과 조인하여 어떤 행을 갱신해야 하는지, 해당 행의 새 값이 무엇이어야 하는지 계산하는 데 도움을 줄 수 있어요. UPDATE-FROM은 SQLite 버전 3.33.0(2020-08-14)부터 지원돼요.

다른 관계형 데이터베이스 엔진도 UPDATE-FROM을 구현하지만, 이 구성은 SQL 표준의 일부가 아니므로 제품마다 UPDATE-FROM을 다르게 구현해요. SQLite 구현은 PostgreSQL과 호환되도록 노력해요. SQL Server와 MySQL의 동일한 개념 구현은 조금 다르게 동작해요.

UPDATE-FROM이 어떻게 유용한지 예를 들어볼게요. 판매 시점(POS) 애플리케이션이 구매 내역을 SALES 테이블에 누적한다고 가정해요. 하루가 끝나면 일일 판매량에 따라 INVENTORY 테이블을 조정하고 싶어요. 이를 위해 INVENTORY 테이블에 대해 해당 일의 집계 판매량만큼 수량을 조정하는 UPDATE를 실행할 수 있어요. 그 문은 다음과 같아요.

UPDATE inventory
   SET quantity = quantity - daily.amt
  FROM (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
 WHERE inventory.itemId = daily.itemId;

FROM 절의 서브쿼리는 각 itemId에 대해 재고가 얼마나 줄어야 하는지를 계산해요. 그 서브쿼리는 inventory 테이블과 조인되고, 영향을 받는 각 재고 행의 quantity는 적절한 양만큼 줄어들어요.

대상 테이블은 대상 테이블에 대한 자기 조인(self-join)을 하려는 경우가 아니라면 FROM 절에 포함되지 않아요. 자기 조인의 경우 FROM 절의 테이블은 대상 테이블과 다른 이름으로 별칭(alias)을 지정해야 해요.

대상 테이블과 FROM 절 사이의 조인 결과 같은 대상 테이블 행에 대해 여러 출력 행이 생성되면, 그중 하나의 출력 행만 대상 테이블을 갱신하는 데 사용돼요. 선택되는 출력 행은 임의적이며 SQLite 릴리스에 따라, 또는 실행할 때마다 달라질 수 있어요.

2.2.1. 다른 SQL 데이터베이스 엔진의 UPDATE FROM

SQL Server도 UPDATE FROM을 지원하지만, SQL Server에서는 대상 테이블이 FROM 절에 포함되어야 해요. 즉, 대상 테이블이 문장에 두 번 명명돼요. SQL Server에서 위에 보인 재고 조정 문은 다음과 같이 작성할 수 있어요.

UPDATE inventory
   SET quantity = quantity - daily.amt
  FROM inventory, 
       (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
 WHERE inventory.itemId = daily.itemId;

MySQL은 UPDATE FROM 개념을 지원하지만, FROM 절을 사용하지 않고 구현해요. 대신 UPDATE와 SET 키워드 사이에 전체 조인 사양을 지정해요. 이에 상응하는 MySQL 문은 다음과 같아요.

UPDATE inventory JOIN
       (SELECT sum(quantity) AS amt, itemId FROM sales GROUP BY 2) AS daily
       USING( itemId )
   SET inventory.quantity = inventory.quantity - daily.amt;

MySQL의 UPDATE 문은 다른 시스템처럼 대상 테이블이 하나만 있는 것은 아니에요. 조인에 참여하는 모든 테이블이 SET 절에서 수정될 수 있어요. MySQL의 UPDATE 구문을 사용하면 여러 테이블을 한 번에 갱신할 수 있어요!

2.3. 선택적인 LIMIT 및 ORDER BY 절 (LIMIT and ORDER BY Clauses)

SQLite를 SQLITE_ENABLE_UPDATE_DELETE_LIMIT 컴파일 타임 옵션으로 빌드하면 UPDATE 문 구문에 선택적 ORDER BY 및 LIMIT 절이 다음과 같이 추가돼요.

update-stmt-limited:

UPDATE 문에 LIMIT 절이 있으면, 함께 오는 표현식을 평가하고 정수 값으로 캐스팅해서 업데이트될 최대 행 수를 구해요. 음수 값은 "제한 없음"으로 해석돼요.

LIMIT 표현식이 음수가 아닌 값 N으로 평가되고 UPDATE 문에 ORDER BY 절이 있으면, LIMIT 절이 없을 때 업데이트되었을 모든 행이 ORDER BY에 따라 정렬된 후 처음 N개 행이 업데이트돼요. UPDATE 문에 OFFSET 절도 있으면, 그것 역시 같은 방식으로 평가되고 정수 값으로 캐스팅돼요. OFFSET 표현식이 음수가 아닌 값 M으로 평가되면, 처음 M개 행은 건너뛰고 그다음 N개 행이 대신 업데이트돼요.

UPDATE 문에 ORDER BY 절이 없으면, LIMIT 절이 없을 때 업데이트되었을 모든 행이 임의의 순서로 모인 다음 LIMIT 및 OFFSET 절을 적용해서 실제로 업데이트할 행을 결정해요.

UPDATE 문의 ORDER BY 절은 LIMIT 범위에 속하는 행을 결정하는 데에만 사용돼요. 행이 수정되는 순서는 임의적이며 ORDER BY 절의 영향을 받지 않아요.