Sharp as a Marble - Just a quick bit of SQL advice

<< It doesn't get any more clingier than this | Home | Because NOTHING else can fire pistol caliber rounds >>

Geekdom

When doing an UPDATE statement, don't forget the WHERE clause.

Otherwise, after running for an inordinately long time, you'll realize that you've just updated millions of rows with the wrong data.

posted @ 9/4/2008 9:52:24 AM

Comments

Sailorcurt says:

on 9/4/2008 10:29:58 AM

I once replaced every entry in a specific field of a 3Gb Oracle database with the same data by running a script with a misplaced semicolon.

We had to restore the database from a day old backup and backfit any updates that had been made that day as a result.

My boss was not impressed.

Robb says:

on 9/4/2008 10:31:07 AM

Well, where I work we have nearly 9 petabytes of DB.

Restoring backups is a several day process ;)

Nomen Nescio says:

on 9/4/2008 12:39:05 PM

data-altering statements should be run twice. first you just do a SELECT COUNT(*) with the same WHERE clause, then after you get a reasonable number back, cut-and-paste in the data-altering bit in the same query string.

Robb says:

on 9/4/2008 1:58:39 PM

Oh, I had already tested and ran it fine, but it was when I copied and pasted the SQL statement into my application to run was when the WHERE statement got dropped.

It's hard to program for "fat fingers"

existingthing says:

on 9/4/2008 3:44:07 PM

... No kidding...

I actually had this question asked of me about a year ago, and I practically wet myself.

"Do you need a where clause in an update statement?"
"NO! STOP! WHAT! STOP WHAT YOU'RE DOING! STEP AWAY FROM THE KEYBOARD! NOW!"

Comments have been closed on this topic.

Comments

Archives

Categories

Comments

Archives

Categories

Second Amendment

Sharpest Marbles

Blogroll

Funny, as in Ha-ha

Good Reads

Extended Blogroll

Industry Links