Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
meef
Posting Yak Master
113 Posts |
Posted - 2013-02-19 : 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 DemoDataSET 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-19 : 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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-19 : 12:47:48
|
if your attempt is to identify the records then use logic likeSELECT *FROM DemoDataWHERE BTSInvDt = DATEADD(year,1,yournewvariable)and it will show you all you updated by mistake------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2013-02-19 : 12:51:23
|
I ran each datetime field with this:UPDATE DemoDataSET BTSInvDt = DATEADD(year, 1, BTSInvDt)So the next one I ran was this:UPDATE DemoDataSET BTSInvDt = DATEADD(year, 1, otherdatefield1)thenUPDATE DemoDataSET 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
52326 Posts |
Posted - 2013-02-19 : 12:53:59
|
yep...it will only affect BTSInvDt column as that was the target of your update------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2013-02-19 : 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
52326 Posts |
Posted - 2013-02-19 : 13:07:04
|
coolglad tht you got it sorted------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|