sql - sql获取单行进行更新
问题描述
我试图能够根据表 A 中每一行的 SDate 在基于 LDate 的日期历史记录中的位置(即第一列 SDate 为2019-01-12 在 2018-10-03 的 LDate 行之后和 2019-04-03 之前,因此表 A 中 Pop 的更新值将是 D 的新值)
表 A
日期 | 线 | 物品 | 流行音乐 |
---|---|---|---|
2019-01-12 | 威克斯 | WP10266 | 乙 |
2019-01-30 | 威克斯 | WP10266 | 乙 |
2019-01-18 | 威克斯 | WP10266 | 乙 |
2019-01-17 | 威克斯 | WP10266 | 乙 |
2019-01-24 | 威克斯 | WP10266 | 乙 |
2019-01-02 | 威克斯 | WP10266 | 乙 |
表 B
日期 | 线 | 物品 | 奥尔德瓦尔 | 新瓦尔 |
---|---|---|---|---|
2019-10-03 | 威克斯 | WP10266 | C | 乙 |
2019-04-03 | 威克斯 | WP10266 | D | C |
2018-10-03 | 威克斯 | WP10266 | W | D |
2018-09-06 | 威克斯 | WP10266 | ñ | W |
以为我可以使用lead()函数在表B上创建一个从/到日期,然后使用日期之间的合并进行更新,如下面的查询,但是我们的iSeries版本是7.2并且它不可用,任何帮助都可以通过另一种方法表示赞赏
merge into tableA as tgt using (
select
src.line,
src.item,
src.ldate as FromDate,
coalesce(lead(src.ldate) over(partition by src.line, src.item order by src.line, src.item, src.ldate) - 1 day, '9999-12-31') as ThruDate,
src.newval
from
tableB
) as src
on tgt.line = src.line
and tgt.item = src.item
and tgt.sdate between src.FromDate and src.ThruDate
when matched then
update
set
tgt.pop = src.newval;
解决方案
通过使用带有订单和限制的横向,您可以从 TABLEB 中挑选出您想要的记录。
Merge Into TableA tgt Using(
Select a.line, a.item, a.sdate, b.newVal
from tableA a
Cross Join lateral (select B.* FROM TableB B
Where B.Ldate <= A.Sdate and B.Line=A.Line and B.item=A.item
Order by B.Ldate Desc
Fetch First 1 Rows Only
) B
) SRC
ON tgt.line = src.line
and tgt.item = src.item
and tgt.sdate = src.sdate
when matched AND tgt.Pop <> src.newVal then update set tgt.Pop = src.newVal
else ignore
由于“Order by”和“Fetch First 1 Rows”,横向连接在这里很好。横向连接标准在其 select 语句中,因此使用“横向交叉连接”很有用。您不需要指定连接条件,因为您已经在横向连接的 Where 子句中做了。
在这里使用合并很有用,因为您可以轻松地一次更新多个列,并在以后包含其他条件。就像“当匹配并且值=值时”。
如果有很多初始更新,Update 语句可能会执行得更好。但那是因为 Merge 而不是 Cross Join。Merge 语句将利用游标来执行工作,这可能会更慢。
知道有数百万个初始更新,我可能会生成另一个表来包含需要更新的内容。然后使用该表更新原始表。
create table qtemp/tablec as (
Select a.line, a.item, a.sdate, a.pop, b.newVal
from tableA a
Cross Join lateral (select B.* FROM TableB B
Where B.Ldate <= A.Sdate and B.Line=A.Line and B.item=A.item
Order by B.Ldate Desc
Fetch First 1 Rows Only ) B
Where a.pop <> b.newval
) with data including defaults;
创建表后,您可以使用它来更新原始表:
Update tablea TGT
set tgt.pop = (Select newval from TABLEC SRC
WHERE tgt.line = src.line
and tgt.item = src.item
and tgt.sdate = src.sdate
)
WHERE EXISTS
(Select * from TABLEC SRC
WHERE tgt.line = src.line
and tgt.item = src.item
and tgt.sdate = src.sdate
AND tgt.pop <> src.newval
)
从技术上讲,您可以在单个更新语句中完成整个更新,但这也需要考虑性能。
Update TableA Tgt
Set Tgt.Pop = (Select newval
From (
Select a.line, a.item, a.sdate, a.pop, b.newVal
from tableA a
Cross Join lateral (select B.* FROM TableB B
Where B.Ldate <= A.Sdate and B.Line=A.Line and B.item=A.item
Order by B.Ldate Desc
Fetch First 1 Rows Only ) B
) SRC
where tgt.line = src.line
and tgt.item = src.item
and tgt.sdate = src.sdate
and tgt.pop <> src.newval
)
Where Exists
(Select newval
From (
Select a.line, a.item, a.sdate, a.pop, b.newVal
from tableA a
Cross Join lateral (select B.* FROM TableB B
Where B.Ldate <= A.Sdate and B.Line=A.Line and B.item=A.item
Order by B.Ldate Desc
Fetch First 1 Rows Only ) B
) SRC
where tgt.line = src.line
and tgt.item = src.item
and tgt.sdate = src.sdate
and tgt.pop <> src.newval
)
;