SQL Server Performance Forum – Threads Archive

Help building a query

Hi folks, I can think of a way to do this in a t-sql, but I’m sure it would be much faster in a single update statement. Is there a function I can use in the set that lets me let me take the larger of two numbers? IE, will this work? UPDATE Target
set startdate=(whichever is less, between Source.Date and Target.Startdate)
set enddate=(whichever is greater, between Source.Date and Target.Enddate)
from Target, Source
where Target.ID = Source.ID
Thanks! Steve Sadler

You can use CASE statement
…
set startdate = case when Source.Date > Target.Startdate then Target.Startdate else Source.Date end
…
or run 2 different queries for both cases.
What I am not clear about is columns of which table are you trying to update (startdate and enddate)
Remember you can only update one table at a time. Bambola.

I think you can achieve this using ‘CASE’. For example:
UPDATE Target
set startdate = (case when Source.Date < Target.Startdate then source.date else Target.Startdate )
from Target, Source
where Target.ID = Source.ID You have to play around with the syntax a little bit.

Thanks to both of you for the help. I’m sorry if my question wasn’t clear, but after seeing your answers I know what needs to be done: UPDATE Target
set startdate = (case when Source.Date < Target.Startdate then source.date else Target.Startdate ),
enddate = (case when Source.Date > Target.EndDate then source.date else Target.EndDate )
from Target, Source
where Target.ID = Source.ID
Now I have a slight variation on the question: If the source table has multiple records with the same ID, then how will it get processed? If I ran it row by row (using a cursor on source, committing the update after every change) then it would turn out like I want. But if I do it in a single update statement (like above), when it grabs the startdate/enddate for an ID that has already been modified, will get the pre transaction view of the data, or the post transaction view of the data? (To phrase my question differently, since it’s still doing one transaction, will it see the updated data when processing multiple records of one ID) Thanks! Steve Sadler

Use GOURP BY with MIN/MAX. use pubs
select type, min(pubdate)
FROM titles
group by type Bambola.

Hey, good thinking! Thanks again, Steve

]]>