我想进行更新有表test,字段为dhhm,type,原数据是这样的:
dhhm type
64894189 1
64567888 1
64518438 1
64518395 1
64561677 1
64532268 1
64561145 1
64560678 1
63701501 1
63701501 1
63731880 1
64867001 1
64867001 1
想进行更新,有重复的只更一个就可以了,该如何实现?
想得到下面的结果:
dhhm type
64894189 0
64567888 0
64518438 0
64518395 0
64561677 0
64532268 0
64561145 0
64560678 0
63701501 0
63701501 1
63731880 0
64867001 0
64867001 1
dhhm type
64894189 1
64567888 1
64518438 1
64518395 1
64561677 1
64532268 1
64561145 1
64560678 1
63701501 1
63701501 1
63731880 1
64867001 1
64867001 1
想进行更新,有重复的只更一个就可以了,该如何实现?
想得到下面的结果:
dhhm type
64894189 0
64567888 0
64518438 0
64518395 0
64561677 0
64532268 0
64561145 0
64560678 0
63701501 0
63701501 1
63731880 0
64867001 0
64867001 1
---------- ----------
64894189 1
64567888 1
64518438 1
64518395 1
64561677 1
64532268 1
64561145 1
64560678 1
63701501 1
63701501 1
63731880 1 DHHM TYPE
---------- ----------
64867001 1
64867001 1已选择13行。SQL> update test b
2 set b.type = 0
3 where b.rowid in (
4 select row_id from ( select rowid as row_id ,
5 row_number() over(partition by dhhm order by rowid )
um from test )
6 where rank_num = 1 ) ;已更新11行。SQL> select * from test; DHHM TYPE
---------- ----------
64894189 0
64567888 0
64518438 0
64518395 0
64561677 0
64532268 0
64561145 0
64560678 0
63701501 0
63701501 1
63731880 0 DHHM TYPE
---------- ----------
64867001 0
64867001 1已选择13行。
set b.type = 0
where b.rowid in(
select min(a.rowid)
from test a
group by a.dhhm,a.type)
update test a set a.name = null
where a.id< (select max(id) id from test b where a.name=b.name)