April 19, 2016 at 6:11 am
Hello all,
I have a table with 2 date columns (StartDate and EndDate).
For some bizarre reason the dates have gone in wrong (in the wrong columns). The startDate should be what the EndDate is and vice-versa.
is it possible to do a quick update? How will the values be retained before they get swapped internally?
update tableA
set StartDate = EndDate,
EndDate = StartDate
where [ID] between 100 and 10000 -- e.g. range of the rows I need to change.
Thank you,
V.K.
April 19, 2016 at 6:14 am
Yup, that will work fine.
Updates are done in two phases, first a read, then a change. So SQL's read all the data it needs from the table before it starts modifying it (if you want to read about why, google 'halloween problem databases' and you should get the details.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 19, 2016 at 6:38 am
Hi Gail,
I will give it a try on our test server.
Interesting concept of Halloween problem in SQL. Reading about it now. Thank you for that.
VK.
April 20, 2016 at 10:45 am
You probably want to verify that the dates are actually backwards before swapping them:
update tableA
set StartDate = EndDate,
EndDate = StartDate
where [ID] between 100 and 10000 -- e.g. range of the rows I need to change
and StartDate < EndDate OR (StartDate IS NULL AND EndDate IS NOT NULL)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply