table1a b c
1 100
2 100
3 230
4 100
5 230
6 310
7 310table2
b c
100 1
230 2
310 3想得到的表table1
a b c
1 100 1
2 100 1
3 230 2
4 100 1
5 230 2
6 310 3
7 310 3求一update语句,得上面的表
update table1 a set a.c=(select b.c from table1 a,table2 b where a.b=b.b)
where exists (select b.c from table1 a,table2 b where a.b=b.b)
提示单行子查询返回多于一行
1 100
2 100
3 230
4 100
5 230
6 310
7 310table2
b c
100 1
230 2
310 3想得到的表table1
a b c
1 100 1
2 100 1
3 230 2
4 100 1
5 230 2
6 310 3
7 310 3求一update语句,得上面的表
update table1 a set a.c=(select b.c from table1 a,table2 b where a.b=b.b)
where exists (select b.c from table1 a,table2 b where a.b=b.b)
提示单行子查询返回多于一行
using(select b, c from table2) B
on (A.b=B.b)
when matched then update set A.c=B.c;
where exists (select 1 from (select x.a,y.c from table1 x,table2 y where x.b = y.b) y where x.a = y.a);
where exists(select 1 from table2 where b=a.b)即可
set c=(select c from table2 where b=table1.b)
where exists (select c from table2 where b=table1.b)
如果是返回多行的话就是你table2表的问题,要保证B字段唯一
否则语句改成
update table1 a set c=(select c from table2 where b=a.b and rownum=1)
where exists(select 1 from table2 where b=a.b)
我不会写出正确的,希望谁能给写个正确的我试试
你的table2有问题
你看下
select * from(
select a.*,count(1)over(partition by b)c from table2 a)
where c>1这些是table2中b字段重复的记录
select c from table2 where b=a.b and rownum=1rownum 是个自增序列,限制rownum=1 表示如果 select c from table2 where b=a.b 返回的结果多于一行,那么只取第一行,这样能保证
set c=(select c from table2 where b=a.b and rownum=1)
不至于把多行的值赋给c,而提示单行子查询返回多于一行
set table1.c = (select table2.c from TABLE2 where table1.b = table2.b )
绝对的ok(用的是orcl)
希望高手能把1楼的语句改一改,在9i上能跑的,我想学学这个语句,再谢了!
where exists(select 1 from table2 where b=a.b)
红色部分是否写上了?应该不会返回多行的merge into 可以看下语法介绍
http://blog.csdn.net/suncrafted/archive/2009/06/29/4306677.aspx
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm9i里面要同时具备when matched和when not matched部分。when not matched then insert values...
看你这部分想怎么改了
table1A B C
1 100
2 100
3 230
4 100
5 230
6 310
7 310
8 500 table2B C
100 1
230 2
310 3
400 4
update table1 a set a.c=(select b.c from table1 a,table2 b where a.b=b.b)
where exists (select b.c from table1 a,table2 b where a.b=b.b)这个语句的执行结果是:
ORA-01427: 单行子查询返回多于一个行
============================
merge into table1 A
using(select b, c from table2) B
on (A.b=B.b)
when matched then update set A.c=B.c;
ORA-00905:缺少关键字
==============================
update table1 x set c = (select y.c from (select x.a,y.c from table1 x,table2 y where x.b = y.b) y where x.a = y.a)
where exists (select 1 from (select x.a,y.c from table1 x,table2 y where x.b = y.b) y where x.a = y.a)
执行后的结果:table1
A B C
1 100 1
2 100 1
3 230 2
4 100 1
5 230 2
6 310 3
7 310 3
8 500
=======================
之后我把table1的c清空了然后是执行下面的语句:
update table1
set c=(select c from table2 where b=table1.b)
where exists (select c from table2 where b=table1.b)执行后的结果:table1
A B C
1 100 1
2 100 1
3 230 2
4 100 1
5 230 2
6 310 3
7 310 3
8 5004楼是对的,但是前提是table2没有重复的行
=================
后来我把table2改成了如下:table2B C
100 1
230 2
310 3
400 4
310 3update table1 a set c=(select c from table2 where b=a.b)
where exists(select 1 from table2 where b=a.b) ORA-01427
单行子查询返回多于一个行
===================
update table1 a set c=(select c from table2 where b=a.b and rownum=1)
where exists(select 1 from table2 where b=a.b)
用这个语句能正确执行,得到正确结果===================
select * from(
select a.*,count(1)over(partition by b) as d from table2 a)
where c>1执行结果
B C D
230 2 1
310 3 2
310 3 2
400 4 1rownum 是个自增序列,限制rownum=1 表示如果 select c from table2 where b=a.b 返回的结果多于一行,那么只取第一行,这样能保证
set c=(select c from table2 where b=a.b and rownum=1)
不至于把多行的值赋给c,而提示单行子查询返回多于一行===================
update table1
set table1.c = (select table2.c from TABLE2 where table1.b = table2.b ) ORA-01427
单行子查询返回多于一个行在SQLSERVER中可以运行,但是ORACLE中不行,ORACLE的语法要严格的多
===================================
如果table2有重复数据,逻辑上就有问题
如果重复的b对应的c不同,要如何更新?
如果相同,又产生冗余
merge into table1 A
using table2 B on (A.b=B.b)
when matched then update set A.c=B.c
when not matched then
insert values(2,b.b,b.c)
提示无法在原表中获得一组稳定值,我写不出正确的,有点丢人了
已写入 file afiedt.buf 1 create table t1
2 ( a int,
3 b int
4 )
5* tablespace myts
SQL> /表已创建。SQL> edi
已写入 file afiedt.buf 1 alter table t1
2* add c int
SQL> /表已更改。
SQL> create table t2
2 (b int,
3 c int)
4 tablespace myts
插入数据
1* insert into t1(a,b) values(1,100)
SQL> /已创建 1 行。SQL> insert into t1(a,b) values(2,100);已创建 1 行。SQL> insert into t1(a,b) values(3,230);已创建 1 行。SQL> insert into t1(a,b) values(4,100);已创建 1 行。SQL> insert into t1(a,b) values(5,230);已创建 1 行。SQL> insert into t1(a,b) values(6,310);已创建 1 行。SQL> insert into t1(a,b) values(7,310);已创建 1 行。SQL> insert into t2(b,c) values(100,1);已创建 1 行。SQL> insert into t2(b,c) values(230,2);已创建 1 行。SQL> insert into t2(b,c) values(310,3);已创建 1 行。 1* merge into t1 using t2 on(t1.b=t2.b) when matched then update set t1.c=t2.c
SQL> /7 行已合并。SQL> select * from t1; A B C
---------- ---------- ----------
1 100 1
2 100 1
3 230 2
4 100 1
5 230 2
6 310 3
7 310 3已选择7行。
merge into table1 using table2 on(table1.b=table2.b) when matched then update set table1.c=table2.c
缺少关键字
正在找这个SQL呢!
踏破铁鞋无觅处得来全不费工夫