更新件数取得

• MySQL

(5.6)

drop table tab1;
create table tab1(col1 int);
insert into tab1 values(1),(2),(3);
select * from tab1;

update tab1 set col1 = col1 + 100
where col1 > 1
;

select row_count();

• Oracle

(12cR1)

drop table tab1 purge;
create table tab1(col1 int);
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab1 values(3);
commit;
select * from tab1;

set serveroutput on;
declare
ret varchar(100);
begin
update tab1 set col1 = col1 + 100
where col1 > 1
;
ret := to_char( SQL%ROWCOUNT );

dbms_output.put_line('SQL%ROWCOUNT = ' || ret );
end;
/

• PostgreSQL

(9.4)

drop table tab1 cascade;
create table tab1(col1 int);
insert into tab1 values(1),(2),(3);
select * from tab1;

update tab1 set col1 = col1 + 100
where col1 > 1 returning *
;

• SQL Server

(2014)

drop table tab1;
create table tab1(col1 int);
insert into tab1 values(1),(2),(3);
select * from tab1;

update tab1 set col1 = col1 + 100
where col1 > 1
;

print '@@ROWCOUNT = ' + cast(@@ROWCOUNT as varchar)