sql to get single row for update

Question

I am trying to be able to update the Pop' column in Table A with the New column value from Table B based on where the SDate for each row in Table A fits in the date history based on LDate (i.e. the first column with SDate of 2019-01-12 is after LDate row with 2018-10-03 and before 2019-04-03 so the updated value for Pop in Table A would be the New value of D)

Table A

SDate	Line	Item	Pop
2019-01-12	WIX	WP10266	B
2019-01-30	WIX	WP10266	B
2019-01-18	WIX	WP10266	B
2019-01-17	WIX	WP10266	B
2019-01-24	WIX	WP10266	B
2019-01-02	WIX	WP10266	B

Table B

LDate	Line	Item	Oldval	NewVal
2019-10-03	WIX	WP10266	C	B
2019-04-03	WIX	WP10266	D	C
2018-10-03	WIX	WP10266	W	D
2018-09-06	WIX	WP10266	N	W

Thought I could use lead() function to create a from/thru date on table B and then just update using a merge between the date like query below, but our iSeries version is 7.2 and its not available, any help is appreciated on another approach

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;

Answer 1

By using a lateral with an order and limit you can pick out the record from TABLEB that you want.

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

The Lateral Join is nice here because of the "Order by" and "Fetch First 1 Rows". The Lateral's join criteria is inside its select statement, so that's why using the "Cross Join Lateral" is useful. You don't need to specify join criteria because you already did on the Where clause of the Lateral Join.

Using the Merge is useful here because you can easily update more than one column at a time and have other conditions included later. Like "When Matched And Value=Value then".

An Update statement would probably perform better if there are lots of initial updates. But that is because of the Merge not the Cross Join. The Merge statement will utilize a cursor to perform the work, which can be slower.

Knowing that there are millions of initial updates, I would probably generate another table to contain what needs to be updated. Then use that table to update the original.

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;

Once the table is created you can use it to update the original:

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
)

You can technically do the entire update in a single update statement, but that will also have performance considerations.

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
)
;

Answer 2

here is how you can do it using cross join :

select * 
from table1 a
cross join (select * from table2 b 
            where a.SDate <= b.Ldate
            and a.line = b.line
            and a.item = b.item 
            order by b.Ldate desc
            LIMIT 1
)

so I guess the update syntax would be:

update table1 a 
SET Pop =  ( select NewVal
             from table2 b 
             where a.SDate <= b.Ldate
             and a.line = b.line
             and a.item = b.item  
             order by b.Ldate desc
             LIMIT 1
           )