在项目中做列表上下移动的时候,遇到这种情况,想根据2行数据的流水号交换移动排序字段的值(因为担心排序字段没值所以加了nvl,空的时候使用流水号):
oracle数据库:
update tableName t set
t.SORT_ROWNUM =
decode(t.num_id,'2968'
,(select nvl(t2.SORT_ROWNUM,t2.num_id) from tableName t2 where t2.num_id ='2969')
,(select nvl(t2.SORT_ROWNUM,t2.num_id) from tableName t2 where t2.num_id ='2968')
) where t.num_id in ('2968','2969');
如果是SQL数据把decode换成case when then else就可以了。
update tableName t set
t.SORT_ROWNUM =
(case when t.num_id = '2968'
then (select t2.SORT_ROWNUM from tableName t2 where t2.num_id ='2969')
else (select t2.SORT_ROWNUM from tableName t2 where t2.num_id ='2968')
end) where t.num_id in ('2968','2969');