MySQLとPostgreSQLは発生する
OracleとSQL Serverは発生しない
(8.0.26)
https://nodoame.net/archives/8026
drop table tab1;
create table tab1(col1 int primary key,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(2,2);
select * from tab1;
update tab1 t1 set col1=(select case when t2.col1=1 then 2 when t2.col1=2 then 1 end from tab1 t2 where t1.col1=t2.col1);
→エラーになる★
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
update tab1 t1 set col1=(select case when t2.col1=1 then 2 when t2.col1=2 then 1 end from (select * from tab1) t2 where t1.col1=t2.col1);
→エラーになる★
ERROR 1062 (23000): Duplicate entry '2' for key 'tab1.PRIMARY'
explain
update tab1 t1 set col1=(select case when t2.col1=1 then 2 when t2.col1=2 then 1 end from (select * from tab1) t2 where t1.col1=t2.col1);
(19c)
drop table tab1 purge;
create table tab1(col1 int primary key,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(2,2);
select * from tab1;
update tab1 t1 set col1=(select case when t2.col1=1 then 2 when t2.col1=2 then 1 end from tab1 t2 where t1.col1=t2.col1);
→エラーなく更新可能
explain plan for
update tab1 t1 set col1=(select case when t2.col1=1 then 2 when t2.col1=2 then 1 end from tab1 t2 where t1.col1=t2.col1);
select * from table(dbms_xplan.display());
(14)
drop table tab1;
create table tab1(col1 int primary key,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(2,2);
select * from tab1;
update tab1 t1 set col1=(select case when t2.col1=1 then 2 when t2.col1=2 then 1 end from tab1 t2 where t1.col1=t2.col1);
→エラーになる★
ERROR: duplicate key value violates unique constraint "tab1_pkey"
DETAIL: Key (col1)=(2) already exists.
explain
update tab1 t1 set col1=(select case when t2.col1=1 then 2 when t2.col1=2 then 1 end from tab1 t2 where t1.col1=t2.col1);
(2019)
drop table tab1;
create table tab1(col1 int primary key,col2 int);
insert into tab1 values(1,1);
insert into tab1 values(2,2);
select * from tab1;
update t1
set t1.col1 = ( case when t2.col1=1 then 2 when t2.col1=2 then 1 end )
from tab1 t1 inner join tab1 t2
on t1.col1=t2.col1
;
→エラーなく更新可能
set showplan_all on
go
update t1
set t1.col1 = ( case when t2.col1=1 then 2 when t2.col1=2 then 1 end )
from tab1 t1 inner join tab1 t2
on t1.col1=t2.col1
;
go
set showplan_all off
go
