有两个表
a
表字段 name ID
表数据 c null
d null
e nullb
表字段 name id date
表数据 c 1 2009-01-01
c 2 2009-02-03
d 1 2009-02-01
d 3 2009-02-02
d 4 2009-01-01
在PL/SQL 中如何直接通过语句把b 表中相应name最新日期的id更新到a表中
有朋友提示说写个过程,不知具体要如何做
a
表字段 name ID
表数据 c null
d null
e nullb
表字段 name id date
表数据 c 1 2009-01-01
c 2 2009-02-03
d 1 2009-02-01
d 3 2009-02-02
d 4 2009-01-01
在PL/SQL 中如何直接通过语句把b 表中相应name最新日期的id更新到a表中
有朋友提示说写个过程,不知具体要如何做
set ID=(select id from b t1 where name=a.name and not exists (select 1 from b where name=t1.name and date>t1.date))
from b
)
where a.name=b.name and b.rn = 1)set a.id = b.id楼主可以试试,我没测试
SET ID = (SELECT TMP_B.ID
FROM (SELECT TMP.ID, TMP.NAME
FROM (SELECT B.ID,
B.NAME,
ROW_NUMBER() OVER(PARTITION BY B.NAME ORDER BY B.DATE1 DESC) AS RN
FROM B) TMP
WHERE TMP.RN = 1) TMP_B
WHERE TMP_B.NAME = A.NAME)
update A set id=(select tem.id from (SELECT B.ID,B.NAME,ROW_NUMBER() OVER(PARTITION BY B.NAME ORDER BY B.V_DATE DESC) RN FROM B) Tem where tem.rn=1 and
tem.name=a.name);