MySQL、PostgreSQL、SQL Serverはインラインビューの更新不可
(8.0.29)
drop table tab1;
create table tab1(
col1 bigint primary key
,col2 bigint
);
drop table tab2;
create table tab2(
col1 bigint primary key
,col2 bigint
);
drop procedure proc1;
delimiter //
create procedure proc1(in x int)
begin
declare i int;
set i = 0;
start transaction;
while i < x do
set i = i + 1;
insert into tab1 values(
i
,1
);
insert into tab2 values(
i
,2
);
end while;
commit;
end
//
delimiter ;
call proc1(100000);
analyze table tab1;
analyze table tab2;
[1]インラインビュー
update (
select
t1.col1 col11
,t1.col2 col12
,t2.col2 col22
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
) t0
set t0.col12 = t0.col22
;
→ ERROR 1288 (HY000): The target table t0 of the UPDATE is not updatable ★
[2]複数テーブル構文
※一致した各行は、条件に複数回一致した場合でも、1 回更新されます。
update tab1 t1,tab2 t2
set t1.col2 = t2.col2
where t1.col1 = t2.col1
;
→更新可能
[3]通常ビュー
drop view view1;
create view view1 as
select
t1.col1 col11
,t1.col2 col12
,t2.col2 col22
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
;
update view1 t0
set t0.col12 = t0.col22
;
→更新可能
[4]サブクエリ
update tab1 t1
set t1.col2 = ( select col2 from tab2 t2 where t2.col1 = t1.col1)
;
→更新可能
(19c)
drop table tab1 purge;
create table tab1(
col1 int constraint tab1pk primary key
,col2 int
);
drop table tab2 purge;
create table tab2(
col1 int constraint tab2pk primary key
,col2 int
);
declare
begin
for i in 1..100000 loop
insert into tab1 values(
i
,1
);
insert into tab2 values(
i
,2
);
end loop;
end;
/
commit;
set time on
set timing on
exec dbms_stats.gather_table_stats(user,'TAB1');
exec dbms_stats.gather_table_stats(user,'TAB2');
[1]インラインビュー
explain plan for
update (
select
t1.col1 col11
,t1.col2 col12
,t2.col2 col22
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
) t0
set t0.col12 = t0.col22
;
select * from table(dbms_xplan.display(format=>'ALL') );
→更新可能。実行計画はHJ
[2]通常ビュー
drop view view1;
create view view1 as
select
t1.col1 col11
,t1.col2 col12
,t2.col2 col22
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
;
explain plan for
update view1 t0
set t0.col12 = t0.col22
;
select * from table(dbms_xplan.display(format=>'ALL') );
→更新可能。実行計画はHJ
[3]サブクエリ
explain plan for
update tab1 t1
set t1.col2 = ( select col2 from tab2 t2 where t2.col1 = t1.col1)
;
select * from table(dbms_xplan.display(format=>'ALL') );
→更新可能。実行計画はNL模様
(14)
drop table tab1 cascade;
create table tab1(
col1 bigint constraint tab1pk primary key
,col2 bigint
);
drop table tab2 cascade;
create table tab2(
col1 bigint constraint tab2pk primary key
,col2 bigint
);
start transaction;
insert into tab1 select
g
,1
from generate_series(1,100000) g;
commit;
start transaction;
insert into tab2 select
g
,2
from generate_series(1,100000) g;
commit;
\timing 1
\pset pager 0
analyze tab1;
analyze tab2;
[1]インラインビュー
update (
select
t1.col1 col11
,t1.col2 col12
,t2.col2 col22
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
) t0
set t0.col12 = t0.col22
;
→ ERROR: syntax error at or near "(" ★
[2]update from構文
update tab1 t1
set col2 = t2.col2
from tab2 t2
where t1.col1 = t2.col1
;
→更新可能
[3]通常ビュー
drop view view1;
create view view1 as
select
t1.col1 col11
,t1.col2 col12
,t2.col2 col22
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
;
update view1 t0
set col12 = t0.col22
;
ERROR: cannot update view "view1" ★
DETAIL: Views that do not select from a single table or view are not automatically updatable.
HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.
[4]サブクエリ
update tab1 t1
set col2 = ( select col2 from tab2 t2 where t2.col1 = t1.col1)
;
→更新可能
※set句のcol2をt1別名修飾するとエラーとなる
(2019)
drop table tab1;
create table tab1(
col1 bigint not null constraint tab1pk primary key
,col2 bigint
);
drop table tab2;
create table tab2(
col1 bigint not null constraint tab2pk primary key
,col2 bigint
);
set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 100000
begin
insert into tab1 values(
@i
,1
);
insert into tab2 values(
@i
,2
);
set @i = @i + 1;
end
commit;
update statistics tab1;
update statistics tab2;
set statistics time on
[1]インラインビュー
update (
select
t1.col1 col11
,t1.col2 col12
,t2.col2 col22
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
)
set col12 = t0.col22
;
→ '(' 付近に不適切な構文があります。 ★
[2]update from構文
update t1
set t1.col2 = t2.col2
from tab1 t1,tab2 t2
where t1.col1 = t2.col1
;
→更新可能
[3]通常ビュー
drop view view1;
go
create view view1 as
select
t1.col1 col11
,t1.col2 col12
,t2.col2 col22
from tab1 t1 inner join tab2 t2
on t1.col1 = t2.col1
;
go
update view1
set col12 = col22
;
→更新可能
※テーブル名や列名を修飾するとエラーとなる
[4]サブクエリ
update tab1
set col2 = ( select col2 from tab2 t2 where t2.col1 = tab1.col1)
;
→更新可能
