SQL-Serverでも Oracleでも
複数に分かれたテーブルを組み合わせて、
SELECT したり、INSERT するのはほぼ同じ書き方で可能。
・結合して SELECT
SELECT t1.Key1 t1_Key1
, t1.Val1 t1_Val1
, t2.Key1 t2_Key1
, t2.Val1 t2_Val1
FROM Table1 t1
JOIN Table2 t2
ON ( t1.Key1 = t2.Key1 )
WHERE ( t1.Key1 Like '%' )
AND ( t2.Key1 Like '%' )
・結合した結果から INSERT
INSERT INTO Table1
( Key1, Val1 )
SELECT t2.Key1
, t2.Val1
FROM Table1 t1
JOIN Table2 t2
ON ( t1.Key1 = t2.Key1 )
WHERE ( t1.Key1 Like '%' )
AND ( t2.Key1 Like '%' )
しかし、組み合わせた結果、片方のテーブルを UPDATE するとか
DELETE するのに毎回悩むので、以下にメモ。
・結合した結果から片方のテーブルを UPDATE
SQL-Server の場合
UPDATE Table1
SET Val1= t2.Val1
FROM Table1 t1
JOIN Table2 t2
ON ( t1.Key1 = t2.Key1 )
WHERE ( t1.Key1 Like '%' )
AND ( t2.Key1 Like '%' )
Oracle の場合
UPDATE (
SELECT t1.Key1 t1_Key1
, t1.Val1 t1_Val1
, t2.Key1 t2_Key1
, t2.Val1 t2_Val1
FROM Table1 t1
JOIN Table2 t2
ON ( t1.Key1 = t2.Key1 )
WHERE ( t1.Key1 Like '%' )
AND ( t2.Key1 Like '%' )
)
SET t1_Val1= t2_Val1
WHERE ( t1_Key1 Like '%' )
AND ( t2_Key1 Like '%' )
・結合した結果から片方のテーブルを DELETE
SQL-Server の場合
DELETE Table1
FROM Table1 t1
JOIN Table2 t2
ON ( t1.Key1 = t2.Key1 )
WHERE ( t1.Key1 Like '%' )
AND ( t2.Key1 Like '%' )
Oracle の場合
DELETE (
SELECT t1.Key1 t1_Key1
, t1.Val1 t1_Val1
, t2.Key1 t2_Key1
, t2.Val1 t2_Val1
FROM Table1 t1
JOIN Table2 t2
ON ( t1.Key1 = t2.Key1 )
WHERE ( t1.Key1 Like '%' )
AND ( t2.Key1 Like '%' )
)
WHERE ( t1_Key1 Like '%' )
AND ( t2_Key1 Like '%' )
