我有两个表,Order
和Intend
。
Order
带有示例数据的表:
oano code slno
---------------
1808 ln 1
1808 lk 2
1808 lc 3
1809 ki 1
1809 dl 2
Intend
带有示例数据的表:
Intendno Oano code slno
------------------------
I/1 1808 ln
I/1 1808 lc
I/2 1809 ki
I/3 1809 dl
我想将slno
从Order
表合并到Intend
表中,如下所示:
Intendno Oano code slno
-----------------------
I/1 1808 ln 1
I/1 1808 lc 3
I/2 1809 ki 1
I/3 1809 dl 2
我如何最好地完成此操作?
使用merge
:
MERGE INTO Intend target
USING (select oano, code, max(slno) slno
from "Order"
group by oano, code
) source
ON
(target.code = source.code and target.Oano = source.Oano
WHEN MATCHED THEN UPDATE
SET target.slno= source.slno;
我们可以尝试使用相关子查询更新:
UPDATE Intend i
SET slno = (SELECT o.slno FROM "Order" o WHERE i.Oano = o.oano AND i.code = o.code);
请注意,命名表Order
是SQL中保留的关键字,这通常是一个坏主意,因为这意味着您每当参考它时都必须逃脱该表名称。
只需使用内部加入
with "Order"(oano, code,slno) as
(
select 1808,'ln',1 from dual union all
select 1808,'lk',2 from dual union all
select 1808,'lc',3 from dual union all
select 1809,'ki',1 from dual union all
select 1809,'dl',2 from dual
), Intend(Intendno, Oano, code) as
(
select 'I/1',1808,'ln' from dual union all
select 'I/1',1808,'lc' from dual union all
select 'I/2',1809,'ki' from dual union all
select 'I/3',1809,'dl' from dual
)
select i.*, o.slno
from Intend i
join "Order" o
on o.Oano = i.Oano
and o.code = i.code;
INTENDNO OANO CODE SLNO
-------- ---- ---- ----
I/1 1808 ln 1
I/1 1808 lc 3
I/2 1809 ki 1
I/3 1809 dl 2
列出所需的结果。
demo