SQL 同じレイアウト同士のテーブルでキーが一致するものを更新するにはどうしたらよいでしょうか

Question

実現したいこと

Aテーブルの一部のレコードが新しく更新されているBテーブルがあるとします。
Bテーブルの内容をAテーブルに反映させるにはどうしたらよいでしょうか。

具体例

tomさんとaaronさんだけを、update_memberの内容で更新したいです。

Aテーブル「member」　カラム　id, name, salary

  1 | tom   |     20
  2 | john  |     30
  3 | aaron |    200

Bテーブル「update_member」　カラム　id, name, salary

  1 | tom   |     18
  3 | aaron |    300

SQL

idが同じものだけBの内容をAに反映したいので、以下のように書きました。

update member a
set (id, name, salary) = 
  (
  select
   id
  ,name
  ,salary
  from
    update_member b
  where
    a.id = b.id
  )
;

結果

結果は以下のようになりました。

  1 | tom   |     18
    |       |
  3 | aaron |    300

なって欲しかったのは↓です。

  1 | tom   |     18
  2 | john  |     30
  3 | aaron |    300

よろしくお願いします。

補足情報

• 上はpostgresで実行しました。
• 業務ではOracleですが、MERGEは使わない実装方法が知りたいです。

Answer 1

上はpostgresで実行しました。

業務ではOracleですが、MERGEは使わない実装方法が知りたいです。

データベースによって方言もありますから、本番と同じデータベース、同じバージョン、同じパッチ環境を用意するのは基本です。

SQLの質問の際は必要な create table, insert も提示できた方が解決が早いです。

create table member
    (
    id      number(5)
  , name    varchar2(128)
  , salary  number(10)
  , constraint pk_member primary key(id) using index
    ) ;

create table update_member
    (
    id      number(5)
  , name    varchar2(128)
  , salary  number(10)
  , constraint pk_update_member primary key(id) using index
    ) ;

insert into member(id, name, salary）values(1, 'tom', 20) ;
insert into member(id, name, salary) values(2, 'john', 30) ;
insert into member(id, name, salary) values(3, 'aaron', 200) ;

insert into update_member(id, name, salary) values(1, 'tom', 18) ;
insert into update_member(id, name, salary) values(3, 'aaron', 300) ;
commit ;

update
    (
    select
        men.id
      , men.name
      , men.salary
      , um.id      as um_id
      , um.name    as um_name
      , um.salary  as um_salary
    from member men
    inner join update_member um
    on men.id = um.id
    ) a
set
    a.name   = a.um_name
  , a.salary = a.um_salary ;

2行が更新されました。

select * from member order by id ;
           ID NAME              SALARY
------------- ---------- -------------
            1 tom                   18
            2 john                  30
            3 aaron                300

Comments

[emk]

ご回答ありがとうございます。

> データベースによって方言もありますから、本番と同じデータベース、同じバージョン、同じパッチ環境を用意するのは基本です。

手元の環境構築を怠ってしまいました。

> SQLの質問の際は必要な create table, insert も提示できた方が解決が早いです。

たしかにその通りですね！今後そのようにします。アドバイスありがとうございます。

Answer 2

updateの方のwhereに

(select id from update_member where a.id = id) IS NOT NULL

をつけるとどうでしょうか？

Comments

[emk]

できました！ありがとうございます。

postgres=# update member a
postgres-# set (id, name, salary) =
postgres-# (
postgres(# select
postgres(# id
postgres(# ,name
postgres(# ,salary
postgres(# from
postgres(# update_member b
postgres(# where
postgres(# a.id = b.id
postgres(# )
postgres-# where (select id from update_member where a.id = id) IS NOT NULL
postgres-# ;
UPDATE 2
postgres=# select * from member order by id;
id | name | salary
----+-------+--------
1 | tom | 18
2 | john | 30
3 | aaron | 300
(3 rows)

Answer 3

検証環境

macOS High Sierra 10.13.2
PostgreSQL 10.1

準備

create table member(
id varchar(255),
name varchar(255),
salary varchar(255)
);

create table update_member(
id varchar(255),
name varchar(255),
salary varchar(255)
);

-- member
INSERT INTO member(id, name, salary) VALUES('1', 'tom', '20');
INSERT INTO member(id, name, salary) VALUES('2', 'john', '30');
INSERT INTO member(id, name, salary) VALUES('3', 'aaron', '200');

-- update_member
INSERT INTO update_member(id, name, salary) VALUES('1', 'tom', '18');
INSERT INTO update_member(id, name, salary) VALUES('3', 'aaron', '300');

回答

そのSQLだとUPDATE分の条件がないため、全件更新しようとしに行きます、
ですが、更新用の値(サブクエリの結果)にjohnがないため、nullで更新されてしまいます。
UPDATE分のWHERE区に

update_memberに該当するidが存在していること

を更新の条件として指定するとうまく行くと思います。

update member a
set (id, name, salary) = 
  (
  select
   id
  ,name
  ,salary
  from
    update_member b
  where
    a.id = b.id
  )
where exists(select '1' from update_member temp where temp.id = a.id )

手元の本(プログラマのためのSQL)だと相関サブクエリを使ってupdateするならexistsを指定するのが良いそうです
また、相関サブクエリの結果が一行にならないと失敗します。
(update_memberのidに一位制約がついていない場合は危険)

手元で確認したエラー

ERROR: more than one row returned by a subquery used as an expression

Comments

[emk]

できました！ありがとうございます。
スッキリわかるSQL入門「ほかのテーブルに値が登場する行のみ抽出したい」時にEXISTS演算子が使えるとありました。不勉強でお恥ずかしいです。

postgres=# update member a
postgres-# set (id, name, salary) =
postgres-# (
postgres(# select
postgres(# id
postgres(# ,name
postgres(# ,salary
postgres(# from
postgres(# update_member b
postgres(# where
postgres(# a.id = b.id
postgres(# )
postgres-# where exists(select '1' from update_member temp where temp.id = a.id )
postgres-# ;
UPDATE 2
postgres=# select * from member order by id;
id | name | salary
----+-------+--------
1 | tom | 18
2 | john | 30
3 | aaron | 300
(3 rows)