連番更新

• MySQL

(8.0.29)
drop table tab1;
create table tab1(col1 bigint,col2 bigint);

set @i:=0; insert into tab1(col1) select row_number() over(order by table_catalog) from information_schema.columns limit 10;
select * from tab1;

set @i:=0; update tab1 set col2 = @i:=@i+1;
select * from tab1;

set @i:=0; update tab1 set col2 = mod(@i:=@i+1,3);
select * from tab1;

• Oracle

(19c)

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

insert into tab1(col1) select rownum from dual connect by level <= 10;
commit;
select * from tab1;

update tab1 set col2 = rownum;
select * from tab1;

update tab1 set col2 = mod(rownum,3);
select * from tab1;

commit;

• PostgreSQL

(15)
drop table tab1;
create table tab1(col1 bigint,col2 bigint);

insert into tab1(col1) select generate_series(1,10);
select * from tab1;

update tab1 t0 set col2 = ( select count(t2.ctid)+1
                            from tab1 t1 left join tab1 t2
                            on t1.ctid > t2.ctid
                            where t1.ctid = t0.ctid )
;
select * from tab1;

update tab1 t0 set col2 = ( select mod(count(t2.ctid)+1,3)
                            from tab1 t1 left join tab1 t2
                            on t1.ctid > t2.ctid
                            where t1.ctid = t0.ctid )
;
select * from tab1;

• SQL Server

(2019)

drop table tab1;
create table tab1(col1 bigint,col2 bigint);

insert into tab1(col1) select top 10 row_number() over(order by object_id) from sys.columns;
select * from tab1;

alter table tab1 add dummy bigint identity(1,1);
select * from tab1;

update tab1 set col2 = dummy;
select * from tab1;

update tab1 set col2 = dummy%3;
select * from tab1;

alter table tab1 drop column dummy;