CODEWITHSUNDEEP
sqlsql-servert-sql
H22
H22

Reputation: 169

Problems shortening a SQL query

I am trying to make a query that works with a temp table, work without that temp table

I tried doing a join in the subquery without the temp table but I don't get the same results as the query with the temp table.

This is the query with the temp table that works as I want:

create table #results(
    RowId       id_t,
    LastUpdatedAt date_T
)

insert into #results
select H.RowId, H.LastUpdatedAt from MemberCarrierMap M Join MemberCarrierMapHistory H on M.RowId = H.RowId

update MemberCarrierMap 
set CreatedAt = (select MIN(LastUpdatedAt) from #results r where r.rowId = MemberCarrierMap.rowId)
Where CreatedAt is null;

and here is the query I tried without the temp table that doesn't work like the above:

update MemberCarrierMap 
set CreatedAt = (select MIN(MH.LastUpdatedAt) from MemberCarrierMapHistory MH join MemberCarrierMap M on MH.RowId = M.RowId where MH.RowId = M.RowId )
Where CreatedAt is null;

I was expecting the 2nd query to work as the first but It is not. Any suggestions on how to achieve what the first query does without the temp table?

Upvotes: 0

Views: 66

Answers (2)

pwilcox
pwilcox

Reputation: 5753

Your question is more or less a duplicate of this answer. There, you will find multiple solutions. But the ones that implement correlated subqueires are less performant than the one that simply uses an uncorrelated aggregation subquery inside a join.

Applying it to your situation, you will have this:

update    m
set       m.createdDate = hAgg.maxVal
from      memberCarrierMap m
join      (
             select   rowId, max(lastUpdatedAt) as maxVal
             from     memberCarrierMapHistory
             group by rowId
          ) as hAgg
          on m.rowId = hAgg.rowId
where     m.createdAt is null;

Basically, it's more performant because it is more expensive to run aggregations and filterings on a row-by-row basis (which is what happens in a correlated subquery) than to just get the aggregations out of the way all at once (joins tend to happen early in processing) and perform the match afterwards.

Upvotes: 0

PeterHe
PeterHe

Reputation: 2766

This should work:

update M
set M.CreatedAt = (select MIN(MH.LastUpdatedAt) from MemberCarrierMapHistory MH WHERE MH.RowId = M.RowId)
FROM MemberCarrierMap M
Where M.CreatedAt is null;

Upvotes: 3

Related Questions