SQL Выбрать запись с датой до даты в объединенной таблице

Вместо использования коррелированного подзапроса в предложении UPDATE, вы можете получить нужные значения через соединение. Во-первых, создайте производную таблицу, которая выглядит практически идентично вашему коррелированному подзапросу, и получите все уникальные значения, необходимые для идентификации строк из #Items, которые вы хотите связать со строками в #Tree. Поскольку нет никаких указаний на уникальные ограничения в упомянутых таблицах, мне пришлось что-то догадываться об этом.

Настройка выборочных данных

-- Setting up sample data
if object_id('tempdb.dbo.#Items') is not null drop table #Items
create table #Items
(
    Item char(1),
    Rev char(2),
    RDate date,
    ECO char(4),
    New bit
)

insert into #Items (Item, Rev, RDate, ECO, New)
values 
    ('A', '0A', '2019-01-01', 'E123', 1),
    ('A', '01', '2018-01-01', 'E456', 0),
    ('B', '0A', '2018-12-31', 'E765', 0),
    ('C', '01', '2019-01-01', 'E456', 0)

if object_id('tempdb.dbo.#Tree') is not null drop table #Tree
create table #Tree
(
    Parent char(1),
    ParentRev char(2),
    Child char(1),
    ChildRev char(2),
    VDate date,
    ECO char(4)
)
insert into #Tree (Parent, ParentRev, Child, ChildRev, VDate)
values
    ('Y', '0B', 'C', NULL, '2019-01-01'),
    ('Y', '0C', 'D', NULL, '2019-01-13'),
    ('Z', '01', 'A', NULL, '2018-06-25'),
    ('Z', '02', 'A', NULL, '2019-01-11'),
    ('Z', '0A', 'B', NULL, '2019-01-01')

Теперь, когда у вас есть производная таблица, отображающая строки в #tree в строки с желаемыми датами из #items, присоединитесь это еще раз к таблице #items, чтобы получить ECO, Rev и все остальное, что вы хотите.

-- Actual Update Statement
update a
set ChildRev = c.Rev,
    Eco = c.Eco
from #Tree a
-- Consruct a derived table basically mapping the rows in #tree to the rows with the desired dates you want.
inner join 
(
    select t.Child, t.ParentRev, MaxRDate = max(i.RDate)
    from #Tree t
    inner join #Items i
        on t.Child = i.Item
            and i.RDate <= t.VDate
    group by t.Child, t.ParentRev
) b
    on a.Child = b.Child
        and a.ParentRev = b.ParentRev
-- Finally, join the "intermidate mapping table" to #Items to get the values (eco, rev, etc.) you actually want
inner join #Items c
    on b.Child = c.Item
        and b.MaxRDate = c.RDate

select top 1000 *
from #Tree

Вообще говоря, это, вероятно, будет работать лучше, чем коррелированный подзапрос, хотя в зависимости от того, какие индексы существуют, ваш пробег может отличаться. Кроме того, если вы действительно просматриваете 4,5 миллиона таких записей, рассмотрите возможность разбить их на партии или найти способ предварительно отфильтровать то, что необходимо обновить, заблаговременно.

Что касается запуска этого процесса, когда появляется новая строка, у вас есть два варианта.

1. В какую бы процедуру не вставлялись данные, которые устанавливают флаг new, она должна запускать этот процесс одновременно (или что-то подобное, чтобы делать оба в одной транзакции).
2. Если это не вариант, теоретически вы можете сделать то же самое с триггером на столе Items, запустив этот процесс по мере необходимости. Хотя TBH я бы порекомендовал первый, так как гораздо проще содержать всю необходимую логику в одном месте и не иметь лишних накладных расходов на наличие триггера, что также несколько запутывает процесс синхронизации данных. [1116 ]

Другая альтернатива

Еще один подход, который я только что разработал, состоит в том, чтобы сделать все в одном запросе. Используйте CTE (или производную таблицу; как хотите) с row_number RID. Затем обновите это, где RID = 1

;with src as
(
    select 
        t.Parent,
        t.ParentRev,
        t.Child,
        t.ChildRev, 
        t.VDate,
        t.ECO,
        Item = i.Item,
        ItemRev = i.Rev,
        ItemRDate = i.RDate,
        ItemECO = i.ECO,
        ItemNew = i.NEW,
        RID = row_number() over (partition by t.Parent, t.ParentRev, t.Child order by i.RDate desc)
    from #Tree t
    inner join #Items i
        on t.Child = i.Item
            and i.RDate <= t.VDate
)
update src
set ECO = ItemECO,
    ChildREv = ItemRev
where RID = 1