表结构如下:
表 A
sell_no(key) order_no code1 code2
U9900001 null A001 B001
U9900002 null null null
U9900003 J9900001 null null
U9900004 J9900002 null null
U9900005 null null null 表 B
sell_no(key) cd1 cd2
U9900001 A001 B001
U9900002 A002 B002
U9900003 A003 B003
U9900004 A004 B004
U9900005 A005 B005 表 C
order_no(key) index(key) no1 no2
J9900001 1 C001 D001
J9900002 1 C002 D002
J9900002 2 C003 D003
J9900003 1 C004 D004 需要对表A的code1,code2字段都为null的数据进行更新
条件:1.当A.order_no为null时,A.sell_no = B.sell_no,取表B的cd1与cd2的值
2.当A.order_no有值时,A.order_no = C.order_no and C.index = '1'
取表C的no1和no2值 求一个oracle中update语句更新表A,得到如下结果:
表 A
sell_no(key) order_no code1 code2
U9900001 null A001 B001
U9900002 null A002 B002
U9900003 J9900001 C001 D001
U9900004 J9900002 C002 D002
U9900005 null A005 B005 第一次发贴,如果有说明不足的地方再补充,谢谢大家
表 A
sell_no(key) order_no code1 code2
U9900001 null A001 B001
U9900002 null null null
U9900003 J9900001 null null
U9900004 J9900002 null null
U9900005 null null null 表 B
sell_no(key) cd1 cd2
U9900001 A001 B001
U9900002 A002 B002
U9900003 A003 B003
U9900004 A004 B004
U9900005 A005 B005 表 C
order_no(key) index(key) no1 no2
J9900001 1 C001 D001
J9900002 1 C002 D002
J9900002 2 C003 D003
J9900003 1 C004 D004 需要对表A的code1,code2字段都为null的数据进行更新
条件:1.当A.order_no为null时,A.sell_no = B.sell_no,取表B的cd1与cd2的值
2.当A.order_no有值时,A.order_no = C.order_no and C.index = '1'
取表C的no1和no2值 求一个oracle中update语句更新表A,得到如下结果:
表 A
sell_no(key) order_no code1 code2
U9900001 null A001 B001
U9900002 null A002 B002
U9900003 J9900001 C001 D001
U9900004 J9900002 C002 D002
U9900005 null A005 B005 第一次发贴,如果有说明不足的地方再补充,谢谢大家
is
cursor c_cursor is
select sell_no,order_no
from A
where code1 is null or code2 is null;
begin
for t_cursor in c_cursor loop
if t_cursor.order_no is null then
update A
set code1 = (select cd1 from B where A.sell_no = B.sell_no),
code2 = (select cd2 from B where A.sell_no = B.sell_no)
where sell_no = t_cursor.sell_no;
else
update A
set code1 = (select no1 from C where A.order_no = C.order_no and C.index = '1'),
code2 = (select no2 from C where A.order_no = C.order_no and C.index = '1')
where sell_no = t_cursor.sell_no;
end if;
end loop;
end test;
SET (code1, code2) =
(SELECT DECODE (a.order_no, NULL, b.cd1, c.no1) code1,
DECODE (a.order_no, NULL, b.cd2, c.no2) code1
FROM b, c
WHERE a.sell_no = b.sell_no(+) AND a.order_no = c.order_no(+)
AND c.index = '1')
WHERE a.code1 IS NULL AND a.code2 IS NULL
这个跟我之前写的一样,但会报错
ORA-01705: 无法在关联列中指定外部连接
我想是不是A.order_no非主键与C表连接的关系忘高人指点谢谢1楼和3楼的,procedure是相对简单多了,做一个判断分别单表更新
但在UPDATE语句里放一块就出错了,就像2楼提供的那样
(select
a.code1 old_code1,
a.code2 old_code2,
decode(a.order_no,null,b.cd1, c.no1) new_code1,
decode(a.order_no,null,b.cd2, c.no2) new_code2
from
b,c
where
a.sell_no = b.sell_no(+)
and a.order_no = c.order_no(+)
and c.index = '1'
and a.code1 is null
and a.code2 is null
)
set
old_code1 = new_code1,
old_code2 = new_code2
网上查了下,还有这种形式的
但也有错
ORA-01779: cannot modify a column which maps to a non-key-preserved table
UPDATE a
SET (code1, code2) =
(SELECT DECODE (a.order_no, NULL, b.cd1, c.no1) code1,
DECODE (a.order_no, NULL, b.cd2, c.no2) code1
FROM a,b, c
WHERE a.sell_no = b.sell_no(+) AND a.order_no = c.order_no(+)
AND c.index = '1'
and a.code1 IS NULL AND a.code2 IS NULL )
WHERE a.code1 IS NULL AND a.code2 IS NULL 这样去试试
ora-01427:single-row subquery returns more than one row
单行子查询返回多
原因还是一样
分开更新当然不会报错
要用procedu
但我是想用update语句来实现
union
select a.sell_no nos,c.no1 code1,c.no2 code2 from a,c where a.order_no is not null and a.order_no=c.order_no and c.indexs=1) d
where a.sell_no=d.nos
)
;
测试了下,的确能正常执行
但少了个条件,在最后加上a.code1 is null and a.code2 is null 就行UPDATE
A SET ( CODE1 ,
CODE2 ) = (
SELECT
CODE1 ,
CODE2
FROM
(
SELECT
A.SELL_NO NOS ,
B.CD1 CODE1 ,
B.CD2 CODE2
FROM
A ,
B
WHERE
A.ORDER_NO IS NULL
AND A.SELL_NO=B.SELL_NO
UNION
SELECT
A.SELL_NO NOS ,
C.NO1 CODE1 ,
C.NO2 CODE2
FROM
A ,
C
WHERE
A.ORDER_NO IS NOT NULL
AND A.ORDER_NO=C.ORDER_NO
AND C.INDEXS=1 ) D
WHERE
A.SELL_NO=D.NOS )
WHERE
A.CODE1 IS NULL
AND A.CODE2 IS NULL
PS:上面表的字段名都是假设的,但没注意到用了index关键字谢谢提醒