この記事の要点
複数カラムを一度に UPDATE する SQL 構文 UPDATE t SET col1 = v1, col2 = v2, col3 = v3 WHERE ... カンマ区切りで複数のカラム = 値を指定 他カラムを参照: SET total = price + tax JOIN UPDATE で他テーブルの値を反映

 

基本構文

-- 複数カラムを同時更新
UPDATE users
SET
    name = 'Alice',
    email = 'alice@example.com',
    updated_at = NOW()
WHERE id = 1;

-- カラム間で参照
UPDATE products
SET
    total = price + tax,
    stock_value = price * stock,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

条件付き UPDATE

-- CASE 式
UPDATE users
SET
    status = CASE
        WHEN last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR) THEN 'INACTIVE'
        WHEN last_login < DATE_SUB(NOW(), INTERVAL 90 DAY) THEN 'DORMANT'
        ELSE 'ACTIVE'
    END,
    updated_at = NOW();

-- 既存値を考慮した更新
UPDATE products
SET
    price = price * 1.1,                  -- 10% 値上げ
    discount = LEAST(discount * 0.9, 30), -- 割引縮小、上限 30
    stock = GREATEST(stock - 1, 0)        -- 在庫減、最低 0
WHERE category = 'electronics';

JOIN UPDATE

MySQL

-- 別テーブルの値を反映
UPDATE users u
INNER JOIN user_profiles p ON u.id = p.user_id
SET
    u.display_name = p.full_name,
    u.avatar_url = p.avatar
WHERE p.is_public = 1;

-- 結合先で集計
UPDATE products p
INNER JOIN (
    SELECT product_id, COUNT(*) AS review_count, AVG(rating) AS avg_rating
    FROM reviews
    GROUP BY product_id
) r ON p.id = r.product_id
SET
    p.review_count = r.review_count,
    p.avg_rating = r.avg_rating;

PostgreSQL

-- FROM 句で他テーブル参照
UPDATE users u
SET
    display_name = p.full_name,
    avatar_url = p.avatar
FROM user_profiles p
WHERE u.id = p.user_id
  AND p.is_public = TRUE;

-- サブクエリで集計
UPDATE products p
SET
    review_count = sub.cnt,
    avg_rating = sub.avg
FROM (
    SELECT product_id, COUNT(*) AS cnt, AVG(rating) AS avg
    FROM reviews
    GROUP BY product_id
) sub
WHERE p.id = sub.product_id;

Oracle

-- MERGE で UPSERT 風
MERGE INTO users u
USING user_profiles p ON (u.id = p.user_id)
WHEN MATCHED THEN
    UPDATE SET
        u.display_name = p.full_name,
        u.avatar_url = p.avatar
    WHERE p.is_public = 1;

-- サブクエリで複数値
UPDATE users u
SET (display_name, avatar_url) = (
    SELECT full_name, avatar
    FROM user_profiles
    WHERE user_id = u.id
)
WHERE EXISTS (SELECT 1 FROM user_profiles WHERE user_id = u.id);

NULL を考慮した更新

-- COALESCE で NULL 安全
UPDATE users
SET
    points = COALESCE(points, 0) + 100,
    last_purchase_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- NULL の場合のみ初期化
UPDATE users
SET
    nickname = COALESCE(nickname, 'ゲスト'),
    bio = COALESCE(bio, '')
WHERE nickname IS NULL OR bio IS NULL;

バッチ UPDATE (大量更新)

-- LIMIT で分割 (MySQL)
UPDATE users
SET status = 'ACTIVE'
WHERE last_login >= '2024-01-01'
LIMIT 1000;

-- ループで繰り返し (アプリ側)
-- 1000 件ずつ更新を繰り返す

-- 統計テーブル更新例 (バッチ)
UPDATE products p
SET
    daily_sales = (
        SELECT SUM(quantity)
        FROM orders o
        WHERE o.product_id = p.id
          AND DATE(o.created_at) = CURRENT_DATE
    ),
    last_calculated = NOW()
WHERE category_id IN (1, 2, 3);

UPSERT (INSERT or UPDATE)

MySQL

-- INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO products (id, name, price, stock)
VALUES (1, 'Widget', 100, 50)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price),
    stock = stock + VALUES(stock);  -- 既存値に加算

-- バルク UPSERT
INSERT INTO products (id, name, price) VALUES
    (1, 'Widget A', 100),
    (2, 'Widget B', 200),
    (3, 'Widget C', 300)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    price = VALUES(price);

PostgreSQL

-- INSERT ... ON CONFLICT ... DO UPDATE
INSERT INTO products (id, name, price, stock)
VALUES (1, 'Widget', 100, 50)
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = products.stock + EXCLUDED.stock;

JPA / Hibernate での複数カラム更新

// Entity 更新 (Dirty Checking)
@Transactional
public void updateUser(Long id, UserUpdateDto dto) {
    User user = userRepository.findById(id).orElseThrow();
    user.setName(dto.getName());
    user.setEmail(dto.getEmail());
    user.setStatus(dto.getStatus());
    // → 自動で UPDATE SQL 生成 (変更されたカラムのみ含まれる)
}

// @Modifying でバルク UPDATE
@Modifying
@Query("UPDATE User u SET u.status = :status, u.updatedAt = CURRENT_TIMESTAMP WHERE u.id IN :ids")
int updateStatusBulk(@Param("ids") List ids, @Param("status") String status);

// CASE WHEN
@Modifying
@Query("""
    UPDATE User u
    SET u.status = CASE
        WHEN u.lastLogin < :inactiveThreshold THEN 'INACTIVE'
        ELSE 'ACTIVE'
    END,
    u.checkedAt = CURRENT_TIMESTAMP
    """)
int categorizeUsers(@Param("inactiveThreshold") LocalDateTime threshold);

注意点

• WHERE 必須: 全行 UPDATE しないよう注意
• トランザクション: 複数更新は @Transactional で囲む
• ロック: 大量更新中は他クエリブロック → バッチ分割推奨
• インデックス: 更新前後で INDEX 再構築が走るためコスト考慮
• トリガ: AFTER UPDATE トリガで連鎖更新が起きないか確認

関連記事

• 現在値に加算する方法
• カラムの追加/削除/変更
• @Modifying