Deleting Column Information - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Deleting Column Information

Author

Topic

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-23 : 10:20:44

HI,
Thank God for backup tables, because I thought I was updating just 1 of 14,012 records, but actually I updated 1 and deleted 14,011 others leaving the field blank. May I get some kind advice on how to not do that next time? Meaning update 1 row and leave the others as is.

Here is what i did...
UPDATE Users
SET Cleared = ( SELECT ULchangefrom.Cleared
FROM ULchangefrom
WHERE ULchangefrom.UserName = Users.UserName);

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 10:45:00

Your update has no WHERE clause so all rows will be affected.
I would always do this:
begin tran

...do the update...

...look at the message how many rows are affectd...

If you think rows affected is ok the do
commit
otherwise do
rollback

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 10:48:35

This way will update only if there is a match in both tables:
update u
set cleared = ul.cleared
from users u
join ULchangefrom ul
on ul.UserName = u.UserName

No, you're never too old to Yak'n'Roll if you're too young to die.

JoshBrigade
Starting Member

28 Posts

Posted - 2009-10-23 : 11:16:20

thanks!

My face was red, my hands shaking.... It was a Live system not a test server!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 14:17:01

quote:
My face was red, my hands shaking.... It was a Live system not a test server!

Welcome to the club!
You're not the first and not the last

No, you're never too old to Yak'n'Roll if you're too young to die.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-10-23 : 16:24:14

The best advice here was the BEGIN TRANSACTION. I don't know how many times I have shown that to developers and other DBA's, just to have them come back and ask how to restore a table because they deleted/updated/inserted something incorrectly.

First things first - use a test system before trying anything on a live system. Second, wrap it up in a transaction as:

BEGIN TRANSACTION;

-- perform insert/update/delete statement

-- Validate results, including a check to verify expected number of rows updated

ROLLBACK TRANSACTION if there are any problems (Note: This is the default in my scripts and has to be commented out

COMMIT TRANSACTION if everything is correct...

A lot of times I'll run the script with it setup to ROLLBACK automatically. I'll put some validation code before the insert/update/delete as well as validation after. Run it so it does the update and then rolls it back - review the results. This will avoid locking the system longer than necessary while I review the results.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-23 : 16:46:26

not always...using transactions on very high volume systems is not acceptable in some environments.

you should always execute a SELECT before an UPDATE or DELETE to make sure it is affecting only the records you want. And you should do it in a dev environment before applying to live. Also, these types of changes should be made during off-peak hours when possible.

For example, say we want to

DELETE FROM users WHERE id > 100

Before executing that, try this

SELECT * FROM users WHERE id > 100

when operating on a live system, there is no excuse for not knowing what records will be affected by a modification.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources