October 6, 2015 at 3:15 am
Hi,
Can you help me please? I'm running a update on a table and it appears to work. But when I check the data about 10 mins later it has been reversed back to the original form! Any ideas/suggestions greatly appreciated...
Thanks
J.
October 6, 2015 at 3:29 am
Either you're running the update in a transaction and not committing it, or someone else is running updates to reverse the changes, or someone's restoring the database from a backup.
The first is probably more likely. If a transaction is not committed and the session disconnects, SQL automatically rolls all the changes back.
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
October 6, 2015 at 3:33 am
I'm just running ad-hoc update, standard syntax, nothing special. I have never experience anythign liek this before. The syntax is:
UPDATE tableName
SET someField = 'newValue'
WHERE someField = 'OldValue'
Command works and no error thrown. However when I look at the table about 5 mins later - its back to origianl.
No triggers on the table. Could it be the permission group thing?
Confused,
J.
October 6, 2015 at 3:44 am
No. Insufficient permissions will get you an error.
If you're sure there's no transaction (and that implicit transactions isn't on), then either someone else is running an update to reverse it, or the database is being restored/reverted. SQL does not randomly undo data modifications.
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
October 6, 2015 at 3:56 am
Sorry - I think it poor SQL on my part is the cause. Better to use syntax like this:
UPDATE someTable
SET soemFieldd= 'New Value'
WHERE key = blah
I'm such a dumba**. Apologies
J.
October 6, 2015 at 3:59 am
There's nothing wrong with the update you posted, and providing there are rows with someField = 'OldValue', it'll work fine and doesn't need to be changed. Now if there are no rows with someField = 'OldValue', then the update needs fixing.
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
October 6, 2015 at 4:07 am
Yes, that's what I thought. But the second SQL (ie the one using the key as unique identifier) is more stable so far. Must be something else kicking in after the 1st SQL command. Strange. No triggers on the table....strange...
October 6, 2015 at 4:18 am
Commands do not undo themselves!
Either your original update didn't actually update any rows, or some other process was explicitly updating the data back. SQL will not randomly undo data modifications
If you run the original update, what's the output in the messages tab? Should be something like "(43 row(s) affected)". What's the output for the revised update?
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
October 6, 2015 at 4:56 am
I do recall seeing message "x rows effected" but it may have been syntax error on my part. I *may* have done something like:
update someTable
set='newValue'
where someField='oldValue'
As I say - this may have happened near eob yesterday...
Anyway - things appear stable now,
Thanks for your feedback
J.
October 6, 2015 at 6:35 am
jellybean (10/6/2015)
I do recall seeing message "x rows effected" but it may have been syntax error on my part. I *may* have done something like:update someTable
set='newValue'
where someField='oldValue'
As I say - this may have happened near eob yesterday...
Anyway - things appear stable now,
Thanks for your feedback
J.
But, that's still a valid UPDATE statement. There has to be some other issue here. You don't get a rollback without a transaction.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply