CODEWITHSUNDEEP
sql-server
user2815625
user2815625

Reputation: 1

How to apply order by on Update?

I am trying to update values in Temporary table but before update i want to order by the records on the basis of date.

UPDATE INS  
  set ins.PrefferedEmail = IC.CntcInfoTxt 
  From #Insured INS
    Inner Join InsuredContact IC
    on IC.InsuredId = INS.Insuredid and IC.ExpDt < Getdate()  And (INS.InsuredStatus = 'Expired' or INS.InsuredStatus = 'Merged') 
    Where IC.CntcTypeCd = 'EML' and IC.InsuredId = @InsuredId and MAX(IC.ExpDt) ExpDt

I want to update on the basis of this column IC.ExpDt

Thanks in advance

Upvotes: 0

Views: 79

Answers (3)

user2815625
user2815625

Reputation: 1

Thanks for your time and comments. I have done this and its working fine

UPDATE INS
set ins.PrefferedEmail = ICC.CntcInfoTxt From #Insured INS Inner Join ( SELECT InsuredId, CntcInfoTxt, CntcTypeCd From InsuredContact ICC Where ExpDt = (select MAX(ExpDt) from InsuredContact where ExpDt < GETDATE() and CntcTypeCd = 'EML' and InsuredId = 10) ) As ICC on ICC.InsuredId = INS.InsuredId And (INS.InsuredStatus = 'Expired' or INS.InsuredStatus = 'Merged')
Where ICC.InsuredId = @InsuredId

Upvotes: 0

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

I think you are confusing an UPDATE with SELECT(ing) the correct data to update with.

I solved this problem with a common table expression (cte) and the rank() function. The cte is a nice way to get a sub-query results. The rank is needed to find the most recent contact info.

-- 1 - Get id, contact text, expired date, with a rank by expired date
-- 2 - Join with table to update, select rank = 1
;
WITH cteRecentContactInfo
AS
(
SELECT 
    ic.InsuredId, 
    ic.CntcInfoTxt, 
    ic.ExpDt, 
    RANK() OVER (ORDER BY ic.ExpDt DESC) as RankByDt
FROM 
    InsuredContact as ic
WHERE 
    ic.CntcTypeCd = 'EML' and ic.ExpDt < getdate()
)

UPDATE ins
FROM #Insured ins INNER JOIN cteRecentContactInfo rci
ON ins.Insuredid = rci.Insuredid and ins.ExpDt = rci.ExpDt
WHERE 
    (ins.InsuredStatus = 'Expired' OR ins.InsuredStatus = 'Merged') AND
    rci.RankByDt = 1

Upvotes: 1

serakfalcon
serakfalcon

Reputation: 3531

Update and sorting doesn't work that way. The rows are not necessarily stored in any particular order, so sorting and updating are completely independent. If you only want to update based on MAX(ExpDt) you need a subquery that pulls that up.

UPDATE INS
SET INS.PrefferedEmail = tempOutside.CntcInfo 
FROM (SELECT IC.CntcInfo,IC.InsuredID FROM 
     InsuredContact IC INNER JOIN
    (SELECT MAX(IC.ExpDt) AS MaxExpDt,IC.InsuredID 
    FROM IC
    WHERE IC.ExpDt < Getdate()
    AND IC.CntcTypeCd = 'EML'
    GROUP BY IC.InsuredID) AS tempInside
ON tempInside.InsuredID = IC.InsuredID
AND IC.ExpDt = tempInside.MaxExpDt) AS tempOutside
INNER JOIN INS ON tempOutside.InsuredID = INS.InsuredId
WHERE (INS.InsuredStatus = 'Expired' OR INS.InsuredStatus = 'Merged')
AND INS.InsuredID = @InsuredID

On unrelated notes, for the good of whoever is doing maintenance on your code you might want to consider fixing the spelling errors (e.g. should be Preferred instead of 'Preffered') and giving the tables more meaningful names. Also since you're only working with one ID from @InsuredID you could simplify the code and remove an inner join or two but what I have should work for updating several records at once not just the one selected by @InsuredID.

Upvotes: 0

Related Questions