SQL:UPDATE文とJOIN句を用いて別のテーブルのデータでテーブルを更新する
SQLで別のテーブルのデータを使ってテーブルを更新する方法
単純なUPDATE
UPDATE テーブル名
SET 列名 = 別テーブル名.列名
FROM テーブル名
JOIN 別テーブル名 ON テーブル名.結合条件 = 別テーブル名.結合条件;
例:customers
テーブルのcity
列をaddresses
テーブルのcity
列で更新
UPDATE customers
SET city = addresses.city
FROM customers
JOIN addresses ON customers.id = addresses.customer_id;
結合条件による更新
UPDATE テーブル名
SET 列名 = 別テーブル名.列名
FROM テーブル名
JOIN 別テーブル名 ON テーブル名.結合条件 = 別テーブル名.結合条件
WHERE テーブル名.条件;
UPDATE customers
SET city = addresses.city
FROM customers
JOIN addresses ON customers.id = addresses.customer_id
WHERE customers.state = 'CA';
サブクエリによる更新
UPDATE テーブル名
SET 列名 = (SELECT 列名 FROM 別テーブル名 WHERE 条件);
FROM テーブル名;
UPDATE customers
SET city = (SELECT city FROM addresses ORDER BY population DESC LIMIT 1);
FROM customers;
注意点
- 更新対象のレコードが正しく選択されていることを確認する。
- 結合条件やWHERE条件を誤ると、意図しないデータが更新される可能性がある。
- テスト環境で動作確認してから本番環境で実行する。
単純なUPDATE
-- テーブル作成
CREATE TABLE customers (
id INT,
name VARCHAR(255),
city VARCHAR(255)
);
INSERT INTO customers (id, name, city) VALUES (1, 'John Doe', 'Tokyo');
INSERT INTO customers (id, name, city) VALUES (2, 'Jane Doe', 'Osaka');
CREATE TABLE addresses (
id INT,
customer_id INT,
city VARCHAR(255)
);
INSERT INTO addresses (id, customer_id, city) VALUES (1, 1, 'New York');
INSERT INTO addresses (id, customer_id, city) VALUES (2, 2, 'London');
-- データ更新
UPDATE customers
SET city = addresses.city
FROM customers
JOIN addresses ON customers.id = addresses.customer_id;
-- 結果確認
SELECT * FROM customers;
-- 結果
-- id | name | city
-- -- | -------- | --------
-- 1 | John Doe | New York
-- 2 | Jane Doe | London
結合条件による更新
-- テーブル作成
CREATE TABLE customers (
id INT,
name VARCHAR(255),
city VARCHAR(255),
state VARCHAR(255)
);
INSERT INTO customers (id, name, city, state) VALUES (1, 'John Doe', 'Tokyo', 'CA');
INSERT INTO customers (id, name, city, state) VALUES (2, 'Jane Doe', 'Osaka', 'NY');
CREATE TABLE addresses (
id INT,
customer_id INT,
city VARCHAR(255)
);
INSERT INTO addresses (id, customer_id, city) VALUES (1, 1, 'New York');
INSERT INTO addresses (id, customer_id, city) VALUES (2, 2, 'London');
-- データ更新
UPDATE customers
SET city = addresses.city
FROM customers
JOIN addresses ON customers.id = addresses.customer_id
WHERE customers.state = 'CA';
-- 結果確認
SELECT * FROM customers;
-- 結果
-- id | name | city | state
-- -- | -------- | -------- | --------
-- 1 | John Doe | New York | CA
-- 2 | Jane Doe | Osaka | NY
サブクエリによる更新
-- テーブル作成
CREATE TABLE customers (
id INT,
name VARCHAR(255),
city VARCHAR(255)
);
INSERT INTO customers (id, name, city) VALUES (1, 'John Doe', 'Tokyo');
INSERT INTO customers (id, name, city) VALUES (2, 'Jane Doe', 'Osaka');
CREATE TABLE addresses (
id INT,
city VARCHAR(255),
population INT
);
INSERT INTO addresses (id, city, population) VALUES (1, 'New York', 1000000);
INSERT INTO addresses (id, city, population) VALUES (2, 'London', 8000000);
-- データ更新
UPDATE customers
SET city = (SELECT city FROM addresses ORDER BY population DESC LIMIT 1);
FROM customers;
-- 結果確認
SELECT * FROM customers;
-- 結果
-- id | name | city
-- -- | -------- | --------
-- 1 | John Doe | London
-- 2 | Jane Doe | London
INSERT INTO ... SELECT ...
-- テーブル作成
CREATE TABLE customers (
id INT,
name VARCHAR(255),
city VARCHAR(255)
);
INSERT INTO customers (id, name) VALUES (1, 'John Doe');
INSERT INTO customers (id, name) VALUES (2, 'Jane Doe');
CREATE TABLE addresses (
id INT,
customer_id INT,
city VARCHAR(255)
);
INSERT INTO addresses (id, customer_id, city) VALUES (1, 1, 'New York');
INSERT INTO addresses (id, customer_id, city) VALUES (2, 2, 'London');
-- データ更新
INSERT INTO customers (city)
SELECT addresses.city
FROM customers
JOIN addresses ON customers.id = addresses.customer_id;
-- 結果確認
SELECT * FROM customers;
-- 結果
-- id | name | city
-- -- | -------- | --------
-- 1 | John Doe | New York
-- 2 | Jane Doe | London
MERGE INTO (Oracle)
-- テーブル作成
CREATE TABLE customers (
id INT,
name VARCHAR(255),
city VARCHAR(255)
);
INSERT INTO customers (id, name) VALUES (1, 'John Doe');
INSERT INTO customers (id, name) VALUES (2, 'Jane Doe');
CREATE TABLE addresses (
id INT,
customer_id INT,
city VARCHAR(255)
);
INSERT INTO addresses (id, customer_id, city) VALUES (1, 1, 'New York');
INSERT INTO addresses (id, customer_id, city) VALUES (2, 2, 'London');
-- データ更新
MERGE INTO customers c
USING addresses a ON (c.id = a.customer_id)
WHEN MATCHED THEN
UPDATE SET c.city = a.city;
-- 結果確認
SELECT * FROM customers;
-- 結果
-- id | name | city
-- -- | -------- | --------
-- 1 | John Doe | New York
-- 2 | Jane Doe | London
UPSERT (MySQL)
-- テーブル作成
CREATE TABLE customers (
id INT,
name VARCHAR(255),
city VARCHAR(255)
);
INSERT INTO customers (id, name) VALUES (1, 'John Doe');
INSERT INTO customers (id, name) VALUES (2, 'Jane Doe');
CREATE TABLE addresses (
id INT,
customer_id INT,
city VARCHAR(255)
);
INSERT INTO addresses (id, customer_id, city) VALUES (1, 1, 'New York');
INSERT INTO addresses (id, customer_id, city) VALUES (2, 2, 'London');
-- データ更新
INSERT INTO customers (id, name, city)
VALUES (1, 'John Doe', 'New York')
ON DUPLICATE KEY UPDATE city = VALUES(city);
-- 結果確認
SELECT * FROM customers;
-- 結果
-- id | name | city
-- -- | -------- | --------
-- 1 | John Doe | New York
-- 2 | Jane Doe | London
それぞれの特徴を理解して、状況に応じて適切な方法を選択してください。
sql oracle sql-update