我有表table1(a,b,new),table2(m,n,old);其中表table1中a,b字段与table2中m,n字段相对应( table1.a=table2.m and table1.b=table2.n),任务:把table2中的old字段数据,更新到table1中的new字段中,注意:记录存在多条,更新的条件存在相同的情况。
update table1 set new=(select old from table2 where table1.a=table2.m and table1.b=table2.n)一条sql就可以吧。如果用存储过程,可以用游标一条条的更新。
merge into t2 tt using (select id , value from t1) st on (tt.id = st.id) when matched then update set value = st.value;
update table1 a set a.new=(select b.old from table2 b where a.a=b.m and a.b=b.n) where exists(select 1 from table2 b where a.a=b.m and a.b=b.n )
--如果为数字型 update table1 set table1.new = (select min(table2.old) from table2 where table1.a=table2.m and table1.b=table2.n )
如果重复,可以选择第一条update table1 set new= ( select old from( select m,n,old from( select m,n,old,row_number() over(partition by m,n,old) rn from table2) where rn=1) b where table1.a=b.m and table1.b=b.n)
update table1 set table1.new = (select k.old from (select table2.old, rownum rn from table2 where table1.a = table2.m and table1.b = table2.n) k where k.rn = 1)
上面的执行都不对,呵呵,谢谢大家帮忙!数据是这样的 table1 table2 a b new m n old 11 12 11 12 wang 11 12 11 12 wang 21 22 21 22 li 21 22 21 22 li 31 34 31 34 song 41 22 51 66 tong .......
更新后: a b new m n old 11 12 wang 11 12 wang 11 12 wang 11 12 wang 21 22 li 21 22 li 21 22 li 21 22 li 31 34 song 31 34 song 41 22 51 66 tong .......
merge into t2 tt using (select id , max(value) value from t1 group by id) st on (tt.id = st.id) when matched then update set value = st.value;
提示row_number() 缺少 order by 不知如何加?
select m,n,old,row_number() over(partition by m,n,old) rn 可以加一个order by rowid变成如下: select m,n,old,row_number() over(partition by m,n,old order by rowid) rn
update table1 a set new = (select distinct b.old from table2 b where a.a = b.m and a.b = b.n) where exists(select 1 from table2 b where a.a=b.m and a.b=b.n)
修改这个,再试一下 update table1 a set a.new=(select distinct b.old from table2 b where a.a=b.m and a.b=b.n) where exists(select 1 from table2 b where a.a=b.m and a.b=b.n
using (select id , value from t1) st
on (tt.id = st.id)
when matched then
update set value = st.value;
update table1 a
set a.new=(select b.old from table2 b where a.a=b.m and a.b=b.n)
where exists(select 1 from table2 b where a.a=b.m and a.b=b.n )
--如果为数字型
update table1 set table1.new = (select min(table2.old) from table2
where table1.a=table2.m and table1.b=table2.n )
set new= (
select old
from(
select m,n,old
from(
select m,n,old,row_number() over(partition by m,n,old) rn
from table2)
where rn=1) b
where table1.a=b.m and table1.b=b.n)
set table1.new = (select k.old
from (select table2.old, rownum rn
from table2
where table1.a = table2.m
and table1.b = table2.n) k
where k.rn = 1)
table1 table2
a b new m n old
11 12 11 12 wang
11 12 11 12 wang
21 22 21 22 li
21 22 21 22 li
31 34 31 34 song
41 22 51 66 tong
.......
a b new m n old
11 12 wang 11 12 wang
11 12 wang 11 12 wang
21 22 li 21 22 li
21 22 li 21 22 li
31 34 song 31 34 song
41 22 51 66 tong
.......
using (select id , max(value) value from t1 group by id) st
on (tt.id = st.id)
when matched then
update set value = st.value;
提示row_number() 缺少 order by 不知如何加?
可以加一个order by rowid变成如下:
select m,n,old,row_number() over(partition by m,n,old order by rowid) rn
set new = (select distinct b.old from table2 b where a.a = b.m and a.b = b.n)
where exists(select 1 from table2 b where a.a=b.m and a.b=b.n)
修改这个,再试一下 update table1 a
set a.new=(select distinct b.old from table2 b where a.a=b.m and a.b=b.n)
where exists(select 1 from table2 b where a.a=b.m and a.b=b.n