update x set dm=1 where exists(select 1 from (select xm,nd,dm from (select xm,nd,dm, row_number() over(partition by xm order by dm) rn from x) where rn=1) t where exists t.xm=x.xm and t.nd=x.nd) --或者用merge into merge into x using (select xm,nd,dm from (select xm,nd,dm, row_number() over(partition by xm order by dm) rn from x) where rn=1) t on (x.xm=t.xm and x.nd=t.nd) when matched then update set dm=1
merge into x t1 using ( select xm,nd,r1 dm from ( select xm,nd,dm,row_number() over(partition by xm order by nd) r1 from x ) t where r1 = 1 ) t2 on (t1.xm = t2.xm and t1.nd = t2.nd) when matched then update set t1.dm = t2.dm when not matched then insert (t1.xm) values (t2.xm);
merge into x using (select xm,min(nd) nd from x group by xm) b on(x.xm=b.xm and x.nd=b.nd) when matched then update set x.dm=1
create table BBB as ( select 'aaa' xm ,1 nd,2 dm from dual union all select 'aaa' xm ,2 nd,2 dm from dual union all select 'aaa' xm ,3 nd,2 dm from dual union all select 'bbb' xm ,4 nd,2 dm from dual union all select 'bbb' xm ,5 nd,2 dm from dual union all select 'bbb' xm ,6 nd,2 dm from dual);update BBB set dm=1 where (xm,nd) in ( select XM,min(ND) from BBB group by XM);select * from BBB; drop table bbb;
update x set dm=1
where exists(select 1 from
(select xm,nd,dm from
(select xm,nd,dm,
row_number() over(partition by xm order by dm) rn
from x)
where rn=1) t
where exists t.xm=x.xm and t.nd=x.nd)
--或者用merge into
merge into x
using (select xm,nd,dm from
(select xm,nd,dm,
row_number() over(partition by xm order by dm) rn
from x)
where rn=1) t
on (x.xm=t.xm and x.nd=t.nd)
when matched then
update
set dm=1
using (
select xm,nd,r1 dm from
(
select xm,nd,dm,row_number() over(partition by xm order by nd) r1
from x
) t
where r1 = 1
) t2
on (t1.xm = t2.xm and t1.nd = t2.nd)
when matched then
update set t1.dm = t2.dm
when not matched then
insert (t1.xm)
values (t2.xm);
merge into x using (select xm,min(nd) nd from x group by xm) b on(x.xm=b.xm and x.nd=b.nd)
when matched then
update set x.dm=1
我这个表X里有很多项,不只xm,dm,nd 这几个,还有很多别的项如 a,b,c等等,也可以用你这个方法吗?
create table BBB as
(
select 'aaa' xm ,1 nd,2 dm from dual
union all
select 'aaa' xm ,2 nd,2 dm from dual
union all
select 'aaa' xm ,3 nd,2 dm from dual
union all
select 'bbb' xm ,4 nd,2 dm from dual
union all
select 'bbb' xm ,5 nd,2 dm from dual
union all
select 'bbb' xm ,6 nd,2 dm from dual);update BBB set dm=1 where (xm,nd) in (
select XM,min(ND) from BBB group by XM);select * from BBB;
drop table bbb;