没环境,没测试过。大概就这样吧。调一下。update A set A.num = temp.thenum from ( select b.id id,case when count(num)>1 then ‘#’else num end thenum from B b inner join A a on a.id = b.id group by b.id )temp where a.id = temp.id 附加: update A set A.num = temp.thenum from ( select b.id id,wm_concat(num) thenum from B b inner join A a on a.id = b.id group by b.id )temp where a.id = temp.id
CREATE TABLE a(id NUMBER, num varchar2(20)); CREATE TABLE b(id NUMBER, num varchar2(20)); --1 UPDATE a SET a.num = (SELECT CASE WHEN COUNT(*) > 1 THEN '#' ELSE MAX(num) END FROM b WHERE b.id = a.id) WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id);--2 UPDATE a SET a.num = (SELECT wm_concat(num) FROM b WHERE b.id = a.id) WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id);
若是9i,wm_concat函数可替换成{sql}select b_id, ltrim(max(sys_connect_by_path(b_name, ',')), ',') b_name from (select b_id, b_name, b_name bn, min(b_name) over(partition by b_id) d_min, (row_number() over(order by b_id, b_name)) + (dense_rank() over(order by b_id)) numid from bb) start with bn = d_min connect by numid - 1 = prior numid group by b_id;
wm_concat这个是标准函数吗?这么神奇?
wm_concat是10g才有的,是一个未公开的函数。
10g以上的可以用listagg函数 update a set a.num = (select listagg(b.num,',') with group (order by num) from b where a.id = b.id )
from
(
select b.id id,case when count(num)>1 then ‘#’else num end thenum from B b
inner join A a on a.id = b.id
group by b.id
)temp
where a.id = temp.id
附加:
update A set A.num = temp.thenum
from
(
select b.id id,wm_concat(num) thenum from B b
inner join A a on a.id = b.id
group by b.id
)temp
where a.id = temp.id
CREATE TABLE a(id NUMBER, num varchar2(20));
CREATE TABLE b(id NUMBER, num varchar2(20));
--1
UPDATE a
SET a.num =
(SELECT CASE
WHEN COUNT(*) > 1 THEN
'#'
ELSE
MAX(num)
END
FROM b
WHERE b.id = a.id)
WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id);--2
UPDATE a
SET a.num =
(SELECT wm_concat(num) FROM b WHERE b.id = a.id)
WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id);
from (select b_id,
b_name,
b_name bn,
min(b_name) over(partition by b_id) d_min,
(row_number() over(order by b_id, b_name)) +
(dense_rank() over(order by b_id)) numid
from bb)
start with bn = d_min
connect by numid - 1 = prior numid
group by b_id;
update a
set a.num =
(select listagg(b.num,',') with group (order by num)
from b
where a.id = b.id
)