PostgreSQL UPDATE JOIN - 다른 테이블 참조해서 수정하기

다른 테이블의 값을 참조해서 UPDATE 해야 할 때가 있다. 예를 들어 주문 테이블의 상품명을 상품 테이블에서 가져와서 수정하는 경우다. PostgreSQL에서는 FROM 절을 사용해서 JOIN UPDATE를 할 수 있다.

MySQL과 문법이 다르다

MySQL에서는 UPDATE에 JOIN을 직접 쓴다.

-- MySQL 문법
UPDATE orders o
JOIN products p ON o.product_id = p.id
SET o.product_name = p.name;

PostgreSQL에서는 이 문법이 안 된다. FROM 절을 사용해야 한다.

-- PostgreSQL 문법
UPDATE orders o
SET product_name = p.name
FROM products p
WHERE o.product_id = p.id;

MySQL에서 PostgreSQL로 마이그레이션 할 때 자주 틀리는 부분이다.

기본 문법

UPDATE
    TABLE_A a
SET
    a.column = b.column
FROM
    TABLE_B b
WHERE
    a.key = b.key;

• FROM 절에 참조할 테이블을 작성
• WHERE 절에 조인 조건을 작성
• SET 절에서 참조 테이블의 컬럼 사용 가능

실습 준비

테스트용 테이블을 만든다.

-- 상품 테이블
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price INT,
    category VARCHAR(50)
);

-- 주문 테이블
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    product_id INT,
    product_name VARCHAR(100),
    product_price INT,
    quantity INT,
    total_amount INT,
    order_date DATE DEFAULT CURRENT_DATE
);

-- 테스트 데이터
INSERT INTO products (name, price, category) VALUES
('노트북', 1500000, '전자기기'),
('마우스', 35000, '주변기기'),
('키보드', 89000, '주변기기'),
('모니터', 450000, '전자기기');

INSERT INTO orders (product_id, product_name, product_price, quantity) VALUES
(1, NULL, NULL, 2),
(2, NULL, NULL, 5),
(3, '구형키보드', 50000, 3),
(4, NULL, NULL, 1);

주문 테이블에 상품 정보가 비어있거나 오래된 상태다.

실습 1: 기본 UPDATE JOIN

상품 테이블에서 이름과 가격을 가져와서 주문 테이블을 수정한다.

UPDATE orders o
SET 
    product_name = p.name,
    product_price = p.price
FROM products p
WHERE o.product_id = p.id;

COMMIT;

결과 확인

SELECT * FROM orders;

 id | product_id | product_name | product_price | quantity | total_amount
----+------------+--------------+---------------+----------+--------------
  1 |          1 | 노트북       |       1500000 |        2 |
  2 |          2 | 마우스       |         35000 |        5 |
  3 |          3 | 키보드       |         89000 |        3 |
  4 |          4 | 모니터       |        450000 |        1 |

구형키보드가 키보드로, 가격도 89000으로 수정됐다.

실습 2: 계산식과 함께 사용

참조한 값으로 계산도 가능하다.

UPDATE orders o
SET 
    product_price = p.price,
    total_amount = p.price * o.quantity
FROM products p
WHERE o.product_id = p.id;

COMMIT;

결과

 id | product_id | product_name | product_price | quantity | total_amount
----+------------+--------------+---------------+----------+--------------
  1 |          1 | 노트북       |       1500000 |        2 |      3000000
  2 |          2 | 마우스       |         35000 |        5 |       175000
  3 |          3 | 키보드       |         89000 |        3 |       267000
  4 |          4 | 모니터       |        450000 |        1 |       450000

상품 가격 × 수량으로 total_amount가 계산됐다.

실습 3: 조건 추가

특정 카테고리 상품만 수정할 수도 있다.

UPDATE orders o
SET product_price = p.price * 0.9  -- 10% 할인
FROM products p
WHERE o.product_id = p.id
AND p.category = '주변기기';

COMMIT;

주변기기(마우스, 키보드)만 10% 할인 가격으로 수정된다.

실습 4: 여러 테이블 JOIN

FROM 절에 여러 테이블을 추가할 수 있다.

-- 카테고리 테이블 추가
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    discount_rate DECIMAL(3,2) DEFAULT 0
);

INSERT INTO categories (name, discount_rate) VALUES
('전자기기', 0.05),
('주변기기', 0.10);

-- 상품 테이블에 category_id 추가
ALTER TABLE products ADD COLUMN category_id INT;
UPDATE products SET category_id = 1 WHERE category = '전자기기';
UPDATE products SET category_id = 2 WHERE category = '주변기기';

여러 테이블을 참조해서 UPDATE

UPDATE orders o
SET 
    total_amount = (p.price * (1 - c.discount_rate)) * o.quantity
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE o.product_id = p.id;

COMMIT;

카테고리별 할인율이 적용된 금액으로 수정된다.

실습 5: 서브쿼리 방식

FROM 절 대신 서브쿼리로도 가능하다.

UPDATE orders
SET product_name = (
    SELECT name 
    FROM products 
    WHERE products.id = orders.product_id
)
WHERE product_id IN (SELECT id FROM products);

동작은 하지만 성능이 좋지 않다. 행마다 서브쿼리가 실행되기 때문이다.

FROM 절 vs 서브쿼리 성능 비교

10만 건 기준 테스트 결과

방법 소요시간 실행계획

FROM 절 (JOIN)	320ms	Hash Join
서브쿼리	4200ms	Nested Loop

FROM 절 방식이 10배 이상 빠르다. 서브쿼리는 행마다 SELECT가 실행되고, FROM 절은 한번에 JOIN 처리된다.

실행계획 확인

UPDATE도 EXPLAIN으로 실행계획을 볼 수 있다.

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders o
SET product_name = p.name
FROM products p
WHERE o.product_id = p.id;

Update on orders o  (cost=... rows=4)
  ->  Hash Join  (cost=... rows=4)
        Hash Cond: (o.product_id = p.id)
        ->  Seq Scan on orders o
        ->  Hash
              ->  Seq Scan on products p

Hash Join이 나오면 정상이다. Nested Loop + Seq Scan 조합이 나오면 인덱스 추가를 고려한다.

자기 자신과 JOIN

같은 테이블 내에서 다른 행을 참조할 수도 있다.

-- 직원 테이블
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT,
    manager_name VARCHAR(50)
);

INSERT INTO employees (name, manager_id) VALUES
('김사장', NULL),
('이부장', 1),
('박과장', 2),
('최대리', 3);

-- 매니저 이름 채우기
UPDATE employees e
SET manager_name = m.name
FROM employees m
WHERE e.manager_id = m.id;

결과

 id |  name  | manager_id | manager_name
----+--------+------------+--------------
  1 | 김사장 |            |
  2 | 이부장 |          1 | 김사장
  3 | 박과장 |          2 | 이부장
  4 | 최대리 |          3 | 박과장

RETURNING과 함께 사용

JOIN UPDATE 결과도 바로 확인할 수 있다.

UPDATE orders o
SET 
    product_name = p.name,
    product_price = p.price
FROM products p
WHERE o.product_id = p.id
RETURNING o.id, o.product_name, o.product_price, p.category;

참조 테이블의 컬럼도 RETURNING에 포함할 수 있다.

주의사항

1. 중복 매칭 주의

FROM 절의 테이블과 1:N 관계면 문제가 생긴다.

-- products에 같은 상품이 여러 개 있으면
UPDATE orders o
SET product_name = p.name
FROM products p
WHERE o.product_id = p.id;
-- 어떤 행이 적용될지 보장 안됨

1:1 또는 N:1 관계에서만 사용하거나, DISTINCT를 활용해야 한다.

2. 별칭 필수

테이블 이름이 길면 별칭을 쓰는 게 좋다.

-- 별칭 없이도 되지만
UPDATE orders
SET product_name = products.name
FROM products
WHERE orders.product_id = products.id;

-- 별칭 쓰는 게 가독성이 좋다
UPDATE orders o
SET product_name = p.name
FROM products p
WHERE o.product_id = p.id;

3. MySQL 마이그레이션

MySQL에서 넘어올 때 문법 변환이 필요하다.

-- MySQL
UPDATE a JOIN b ON a.id = b.a_id SET a.col = b.col;

-- PostgreSQL
UPDATE a SET col = b.col FROM b WHERE a.id = b.a_id;

JOIN 위치가 다르다는 것만 기억하면 된다.

정리

• PostgreSQL UPDATE JOIN은 FROM 절 사용
• MySQL과 문법이 다름 (JOIN → FROM)
• 서브쿼리보다 FROM 절이 10배 이상 빠름
• 여러 테이블 JOIN 가능
• RETURNING과 함께 사용 가능
• 1:N 관계에서는 중복 매칭 주의