Help - Sql update commands being reversed

Question

Help - Sql update commands being reversed

jellybean

SSCrazy

Points: 2014
More actions
October 6, 2015 at 3:15 am

#312061

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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jellybean SSCrazy Points: 2014 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jellybean SSCrazy Points: 2014 More actions · Answer 4

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jellybean SSCrazy Points: 2014 More actions · Answer 6

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...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jellybean SSCrazy Points: 2014 More actions · Answer 8

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.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 9

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