두 테이블에 걸친 UPDATE와 DELETE

다른 테이블의 값을 기준으로 UPDATE나 DELETE를 할 때는 엔진별 문법 차이가 큽니다. 먼저 어디서나 쓸 수 있는 서브쿼리 형태를 보고, 이어서 엔진별 조인 문법을 비교합니다.

이 페이지는 데이터를 바꿉니다. 브라우저 안의 여러분 사본을 바꿉니다. 처음으로 되돌리려면 환영 화면에서 샘플을 다시 불러오세요.

어디서나 도는 형태

UPDATE의 WHERE에서도 서브쿼리로 변경할 행을 고를 수 있습니다. 이 방식은 별도 문법 없이 모든 엔진에서 사용할 수 있습니다.

1

-- Works on every engine in this list, unchanged.
UPDATE customers
SET loyalty_points = loyalty_points + 50
WHERE id IN (
  SELECT customer_id FROM orders WHERE status = 'refunded' AND total > 2000
);
Planche에서 실행

242행 변경

브라우저의 새 탭에서 Planche가 열리고, 샘플 데이터베이스가 로드된 채로 이 쿼리가 들어갑니다. 이 페이지의 다른 예제도 같은 탭을 다시 씁니다. F9를 누르기 전까지는 아무것도 실행되지 않습니다.

새 값이 다른 테이블에서 와야 한다면 SET 안에 상관 서브쿼리를 사용할 수 있습니다.

2

-- The same shape when the new value comes from the other table.
UPDATE products
SET stock_quantity = stock_quantity + (
  SELECT COALESCE(SUM(change_qty), 0)
  FROM inventory_logs il
  WHERE il.product_id = products.id
    AND il.reason = 'return'
)
WHERE id IN (SELECT product_id FROM inventory_logs WHERE reason = 'return');
Planche에서 실행

203행 변경

조건이 두 번 나옵니다. SET의 서브쿼리는 새 값을 정하고, WHERE의 서브쿼리는 변경할 행을 고릅니다. 두 번째 조건이 없으면 모든 상품이 갱신 대상이 됩니다. 로그가 없는 상품은 COALESCE(..., 0) 때문에 값이 같아도 쓰기 작업은 발생합니다.

이 형태는 이식성이 높고 읽기 쉽습니다. 다만 엔진에 따라 상관 서브쿼리를 행마다 실행할 수 있어 항상 가장 빠르지는 않습니다. 갱신 대상이 많다면 실행 계획을 확인한 뒤 아래의 엔진별 조인 문법을 검토하세요.

MySQL과 MariaDB

MySQL / MariaDB

-- The join goes between UPDATE and SET.
UPDATE customers c
  JOIN orders o ON o.customer_id = c.id
SET c.loyalty_points = c.loyalty_points + 10
WHERE o.status = 'refunded';

-- DELETE names which table's rows to remove, before FROM.
DELETE c
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

MySQL / MariaDB — 비교용입니다. 브라우저 샘플 데이터베이스는 SQLite라서 이 문장은 여기서 실행할 수 없습니다.

MySQL과 MariaDB에서는 조인을 UPDATE와 SET 사이에 작성합니다. DELETE에서는 FROM 앞에 삭제 대상 별칭을 지정해야 합니다(DELETE c FROM customers c JOIN ...). 조인된 테이블 가운데 어느 쪽의 행을 삭제할지 분명히 하기 위해서입니다.

PostgreSQL

PostgreSQL

-- The second table goes in FROM, and the join condition in WHERE.
UPDATE customers
SET loyalty_points = loyalty_points + 10
FROM orders o
WHERE o.customer_id = customers.id
  AND o.status = 'refunded';

-- DELETE uses USING rather than FROM for the extra table.
DELETE FROM wishlists
USING products p
WHERE p.id = wishlists.product_id
  AND p.stock_quantity = 0;

PostgreSQL — 비교용입니다. 브라우저 샘플 데이터베이스는 SQLite라서 이 문장은 여기서 실행할 수 없습니다.

PostgreSQL에서는 추가 테이블을 FROM에 쓰고 조인 조건을 WHERE에 둡니다. 대상 테이블을 FROM에 다시 쓰면 자기 자신과 조인하게 되므로 주의하세요. 또한 UPDATE는 FROM을, DELETE는 USING을 사용합니다.

SQLite는 3.33(2020년)부터 PostgreSQL과 같은 의미의 UPDATE … FROM을 지원합니다. 따라서 이 페이지의 세 번째 예제도 SQLite에서 실행할 수 있습니다.

3

-- SQLite 3.33 (2020) and later, with PostgreSQL's spelling.
UPDATE customers
SET loyalty_points = loyalty_points + 10
FROM orders o
WHERE o.customer_id = customers.id
  AND o.status = 'refunded'
  AND o.total > 2500;
Planche에서 실행

224행 변경

SQLite에는 DELETE … USING이 없어서 삭제는 여전히 서브쿼리로 합니다.

SQL Server

SQL Server

-- The alias is updated, and the FROM clause repeats the target table.
UPDATE c
SET c.loyalty_points = c.loyalty_points + 10
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

DELETE c
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

SQL Server — 비교용입니다. 브라우저 샘플 데이터베이스는 SQLite라서 이 문장은 여기서 실행할 수 없습니다.

SQL Server에서는 UPDATE 대상에 별칭을 쓰고, FROM에서 그 별칭과 대상 테이블을 다시 지정합니다. 테이블 이름이 한 문장에 두 번 나오지만 T-SQL에서 사용하는 형식이며 DELETE도 같은 패턴을 따릅니다.

Oracle

Oracle

-- Oracle has no UPDATE ... FROM and no UPDATE ... JOIN.
-- EXISTS is the portable answer, and the one to use.
UPDATE customers c
SET c.loyalty_points = c.loyalty_points + 10
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'refunded'
);

DELETE FROM wishlists w
WHERE EXISTS (
  SELECT 1 FROM products p
  WHERE p.id = w.product_id AND p.stock_quantity = 0
);

Oracle — 비교용입니다. 브라우저 샘플 데이터베이스는 SQLite라서 이 문장은 여기서 실행할 수 없습니다.

Oracle에는 UPDATE … JOIN이나 UPDATE … FROM 문법이 없습니다. 상관 서브쿼리와 EXISTS를 사용하는 방식이 일반적이며, 다른 엔진에서도 사용할 수 있습니다.

다른 방법은 갱신 가능한 조인 뷰를 쓰는 것입니다(UPDATE (SELECT a.col, b.col FROM a JOIN b ON …) SET …). Oracle은 조인이 키를 보존한다는 것을 확인할 수 있을 때만 이를 허용합니다. 조인 컬럼에 유니크 제약이나 기본 키가 있어야 하며, 그렇지 않으면 ORA-01779 오류가 납니다. 이 경우에는 EXISTS 방식으로 돌아가야 합니다.

실행하기 전에

Planche가 UPDATE와 DELETE를 실행 전에 대응하는 SELECT로 바꾸어 보여 주는 이유가 여기에 있습니다. 조인이 들어가면 일반적인 WHERE보다 영향 범위를 파악하기 어렵습니다. 설정 › 안전장치 › 영향 행 미리보기를 켜면 먼저 개수와 표본을 확인할 수 있습니다. 이 기능은 위의 다섯 형태를 모두 처리하며, 저장소의 검증 스크립트는 예상 행 수와 실제 변경 행 수가 일치하는지 각 엔진에서 확인합니다.