別テーブルの値を使用してupdate

サブクエリ構文は4DBで使用可能
MySQLは複数テーブル構文を使用可能。しかし複数行該当のエラーを検知できない
PostgreSQLはupdate from構文を使用可能。しかし複数行該当のエラーを検知できない
SQL Serverはupdate from構文を使用可能。しかし複数行該当のエラーを検知できない

したがって、常にサブクエリ構文を使用するべきと思われる。

• MySQL

(8.0.22)

drop table tab1;
drop table tab2;
create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

insert into tab1 values(1,0);
insert into tab1 values(2,0);

insert into tab2 values(1,10);
insert into tab2 values(2,20);
insert into tab2 values(2,21);

select * from tab1;
select * from tab2;

-- 1.サブクエリ構文
update tab1 A
set col2 = ( select B.col2 from tab2 B where B.col1 = A.col1)
where A.col1 = 1
;

update tab1 A
set col2 = ( select B.col2 from tab2 B where B.col1 = A.col1)
where A.col1 = 2
;
ERROR 1242 (21000): Subquery returns more than 1 row

-- 2. update from構文
update tab1 A
set col2 = B.col2
from tab2 B
where A.col1 = B.col1
and A.col1 = 1
;
ERROR 1064 (42000): You have an error in your SQL syntax

update tab1 A
set col2 = B.col2
from tab2 B
where A.col1 = B.col1
and A.col1 = 2
;
ERROR 1064 (42000): You have an error in your SQL syntax

-- 3.複数テーブル構文
update tab1 A,tab2 B
set A.col2 = B.col2
where A.col1 = B.col1
and A.col1 = 1
;

update tab1 A,tab2 B
set A.col2 = B.col2
where A.col1 = B.col1
and A.col1 = 2
;

★エラーにならない

• Oracle

(19c)

drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

insert into tab1 values(1,0);
insert into tab1 values(2,0);

insert into tab2 values(1,10);
insert into tab2 values(2,20);
insert into tab2 values(2,21);

select * from tab1;
select * from tab2;

commit;

-- 1.サブクエリ構文
update tab1 A
set col2 = ( select B.col2 from tab2 B where B.col1 = A.col1)
where A.col1 = 1
;

update tab1 A
set col2 = ( select B.col2 from tab2 B where B.col1 = A.col1)
where A.col1 = 2
;
ORA-01427: 単一行副問合せにより2つ以上の行が戻されます

-- 2. update from構文
update tab1 A
set A.col2 = B.col2
from tab2 B
where A.col1 = B.col1
and A.col1 = 1
;

ORA-00933: SQLコマンドが正しく終了されていません。

update tab1 A
set A.col2 = B.col2
from tab2 B
where A.col1 = B.col1
and A.col1 = 2
;

ORA-00933: SQLコマンドが正しく終了されていません。

-- 3.複数テーブル構文
update tab1 A,tab2 B
set A.col2 = B.col2
where A.col1 = B.col1
and A.col1 = 1
;
ORA-00971: SETキーワードがありません。

update tab1 A,tab2 B
set A.col2 = B.col2
where A.col1 = B.col1
and A.col1 = 2
;
ORA-00971: SETキーワードがありません。

(23c)

drop table tab1 purge;
drop table tab2 purge;
create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

insert into tab1 values(1,0);
insert into tab1 values(2,0);

insert into tab2 values(1,10);
insert into tab2 values(2,20);
insert into tab2 values(2,21);

select * from tab1;
select * from tab2;

commit;

-- 1.サブクエリ構文
update tab1 A
set col2 = ( select B.col2 from tab2 B where B.col1 = A.col1)
where A.col1 = 1
;

update tab1 A
set col2 = ( select B.col2 from tab2 B where B.col1 = A.col1)
where A.col1 = 2
;

ORA-30926: 同じ行を更新しようとしました。

-- 2. update from構文
update tab1 A
set A.col2 = B.col2
from tab2 B
where A.col1 = B.col1
and A.col1 = 1
;

★23cから実行可能

update tab1 A
set A.col2 = B.col2
from tab2 B
where A.col1 = B.col1
and A.col1 = 2
;

ORA-30926: 同じ行を更新しようとしました。

-- 3.複数テーブル構文
update tab1 A,tab2 B
set A.col2 = B.col2
where A.col1 = B.col1
and A.col1 = 1
;
ORA-00971: SETキーワードがありません。

update tab1 A,tab2 B
set A.col2 = B.col2
where A.col1 = B.col1
and A.col1 = 2
;
ORA-00971: SETキーワードがありません。

• PostgreSQL

(13)

drop table tab1;
drop table tab2;
create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

insert into tab1 values(1,0);
insert into tab1 values(2,0);

insert into tab2 values(1,10);
insert into tab2 values(2,20);
insert into tab2 values(2,21);

select * from tab1;
select * from tab2;

-- 1.サブクエリ構文
update tab1 A
set col2 = ( select B.col2 from tab2 B where B.col1 = A.col1)
where A.col1 = 1
;

update tab1 A
set col2 = ( select B.col2 from tab2 B where B.col1 = A.col1)
where A.col1 = 2
;
ERROR: more than one row returned by a subquery used as an expression

-- 2. update from構文
update tab1 A
set col2 = B.col2
from tab2 B
where A.col1 = B.col1
and A.col1 = 1
;

update tab1 A
set col2 = B.col2
from tab2 B
where A.col1 = B.col1
and A.col1 = 2
;
★エラーにならない

-- 3.複数テーブル構文
update tab1 A,tab2 B
set A.col2 = B.col2
where A.col1 = B.col1
and A.col1 = 1
;
ERROR: syntax error at or near ","

update tab1 A,tab2 B
set A.col2 = B.col2
where A.col1 = B.col1
and A.col1 = 2
;
ERROR: syntax error at or near ","

• SQL Server

(2019)

drop table tab1;
drop table tab2;
create table tab1(col1 int,col2 int);
create table tab2(col1 int,col2 int);

insert into tab1 values(1,0);
insert into tab1 values(2,0);

insert into tab2 values(1,10);
insert into tab2 values(2,20);
insert into tab2 values(2,21);

select * from tab1;
select * from tab2;

-- 1.サブクエリ構文
update tab1
set col2 = ( select B.col2 from tab2 B where B.col1 = col1 and col1 = 1)
where col1 = 1
;

update tab1
set col2 = ( select B.col2 from tab2 B where B.col1 = col1 and col1 = 2)
where col1 = 2
;
サブクエリは複数の値を返しました。サブクエリが =、!=、<、<=、>、>= の後に続く場合や、サブクエリが 1 つの式として使われる場合は複数の値は許可されません。

-- 2. update from構文
update A
set A.col2 = B.col2
from tab1 A,tab2 B
where A.col1 = B.col1
and A.col1 = 1
;

update A
set A.col2 = B.col2
from tab1 A,tab2 B
where A.col1 = B.col1
and A.col1 = 2
;
★エラーにならない

-- 3.複数テーブル構文
update tab1 A,tab2 B
set A.col2 = B.col2
where A.col1 = B.col1
and A.col1 = 1
;
'A' 付近に不適切な構文があります。

update tab1 A,tab2 B
set A.col2 = B.col2
where A.col1 = B.col1
and A.col1 = 2
;
'A' 付近に不適切な構文があります。