Problems shortening a SQL query

Question

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?

At a glance, they look like they would give the same results (although I think your WHERE clause in the 2nd query is redundant given the join). Can you describe how the results are different? — jefftrotman, Jul 25 '19 at 18:27
The second query only gets the minimum for the whole table. The first one gets the minimum for that RowId. Thanks. — H22, Jul 25 '19 at 18:30

PeterHe · Accepted Answer · 2019-07-25T19:09:34.150

3

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;

edited Jul 25 '19 at 19:09

answered Jul 25 '19 at 18:29

PeterHe

2,766
1
8
7

The subquery needs to eb correlated with the main table being updated. – PeterHe Jul 25 '19 at 18:30
@PeterHe, you need to put 'set' statement before 'from' statement – pwilcox Jul 25 '19 at 18:52
@PeterHe yes change it to set statement before the from statement and I will accept the answer – H22 Jul 25 '19 at 18:57
Thanks@pwilcox and @H22 , too much mysql stuff recently. – PeterHe Jul 25 '19 at 19:09

pwilcox · Answer 2 · 2019-07-25T19:15:58.760

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.

Problems shortening a SQL query

2 Answers2