| Author |
 Topic  |
|
|
meef
Yak Posting Veteran
90 Posts |
 Posted - 02/19/2013 : 12:41:28
|
I have a dummy table here with 27000 records and they all had dates from 2008 through 2012. I wanted to go through and add one year to each column, using this line:
UPDATE DemoData
SET BTSInvDt = DATEADD(year, 1, BTSInvDt)
Now that worked fine, but then I was an idiot and only changed the second variable (in this case, the second BTSINVDT) and now they all have the same year. What's the best way to get a range of dates back into these records? |
|
|
James K
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 02/19/2013 : 12:44:43
|
quote:
only changed the second variable (in this case, the second BTSINVDT)
So what was the query that you actually ran? There is no easy way to roll back the changes you already committed. You can try to restore a backup to another location and try to retrieve the correct data from the restored copy. Unless you have another table in the database that can be used to compute what the dates should be, that would be your best bet. |
Edited by - James K on 02/19/2013 12:45:51 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47188 Posts |
Posted - 02/19/2013 : 12:47:48
|
if your attempt is to identify the records then use logic like
SELECT *
FROM DemoData
WHERE BTSInvDt = DATEADD(year,1,yournewvariable)
and it will show you all you updated by mistake
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
meef
Yak Posting Veteran
90 Posts |
Posted - 02/19/2013 : 12:51:23
|
I ran each datetime field with this:
UPDATE DemoData
SET BTSInvDt = DATEADD(year, 1, BTSInvDt)
So the next one I ran was this:
UPDATE DemoData
SET BTSInvDt = DATEADD(year, 1, otherdatefield1)
then
UPDATE DemoData
SET BTSInvDt = DATEADD(year, 1, otherdatefield2)
Etc. etc. I left BTSInvDt in the beginning for each one instead of changing both of them.
Oddly enough, somehow everything looks good again, maybe it didn't change everything I thought it did and only did the BTSInvDt column.
This probably doesn't make any sense, but bottom line I think all is good :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47188 Posts |
Posted - 02/19/2013 : 12:53:59
|
yep...it will only affect BTSInvDt column as that was the target of your update
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
meef
Yak Posting Veteran
90 Posts |
Posted - 02/19/2013 : 13:04:47
|
| Yeah, so when I went back to BTSInvDt to just fix the month/date (because everything was the same day/month/year in the field), I took the data from another date field minus one week so it added all the proper years and months back in there too. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47188 Posts |
Posted - 02/19/2013 : 13:07:04
|
cool
glad tht you got it sorted
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Adding 1 year to all dates, but messed up
