Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

An UPDATE without a WHERE, or something close to it

rachelbythebay.com

Post reply on HN
Print view View original

81–90 of 112 posts

Re: An UPDATE without a WHERE, or something close to it

#81

Post by gfody »

codeulike wrote: post #14

re: UPDATE without a WHERE In SSMS (the main query console tool thing for Sql Server) you can highlight a bit of code with the cursor (as if you want to copy/paste it) and press CTRL-E to execute it, its really handy when you've got a big sketchpad-like series of SELECT statements and you're doing exploratory tinkering. But if your UPDATE statement is accross three lines, its a little bit too easy to accidentally sel…

I once saw a coworker get destroyed by a rendering glitch in SSMS - he had GPU acceleration enabled and didn't notice his text selection highlight glitching out, smashed F5 on a query that looked like it had a where clause selected but if you fiddled with the scrollbar suddenly it wasn't actually selected. I noticed his query blocking everything and killed it, walked over to his office like WTF and he reran it right in front of me reproducing the glitch exactly - he couldn't believe what he'd done. disabling GPU acceleration in SSMS fixed it.

Re: An UPDATE without a WHERE, or something close to it

#82

Post by zimpenfish »

jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

> A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE.

Given the annoyance of this and its ability to really, really ruin your day, I don't know why someone hasn't updated their parser to allow 'update X where [cond] set [blah]' as an alternate phrasing.

Re: An UPDATE without a WHERE, or something close to it

#83

Post by rspeele »

codeulike wrote: post #14

re: UPDATE without a WHERE In SSMS (the main query console tool thing for Sql Server) you can highlight a bit of code with the cursor (as if you want to copy/paste it) and press CTRL-E to execute it, its really handy when you've got a big sketchpad-like series of SELECT statements and you're doing exploratory tinkering. But if your UPDATE statement is accross three lines, its a little bit too easy to accidentally sel…

Oh yeah, it could happen. Happened to a, um, friend of mine. This friend never uses that feature anymore, no matter how convenient it looks.

Re: An UPDATE without a WHERE, or something close to it

#84

Post by rspeele »

gfody wrote: post #81
codeulike wrote: post #14

re: UPDATE without a WHERE In SSMS (the main query console tool thing for Sql Server) you can highlight a bit of code with the cursor (as if you want to copy/paste it) and press CTRL-E to execute it, its really handy when you've got a big sketchpad-like series of SELECT statements and you're doing exploratory tinkering. But if your UPDATE statement is accross three lines, its a little bit too easy to accidentally sel…

I once saw a coworker get destroyed by a rendering glitch in SSMS - he had GPU acceleration enabled and didn't notice his text selection highlight glitching out, smashed F5 on a query that looked like it had a where clause selected but if you fiddled with the scrollbar suddenly it wasn't actually selected. I noticed his query blocking everything and killed it, walked over to his office like WTF and he reran it right…

The GPU accelerated text rendering in SSMS 18 is incredibly bad. The other thing it likes to do sometimes is continue showing the text from tab A when you switch to tab B, until you scroll (not possible in short snippets) or edit it (and only the edited part updates!)

Re: An UPDATE without a WHERE, or something close to it

#85

Post by marcosdumay »

dcminter wrote: post #77
marcosdumay wrote: post #53

Earlier quoted context omitted.

Oh, locking is what Oracle does. That and hanging because the work of keeping the transaction increases faster than linearly with the amount of data. You shouldn't get access to prod if you didn't have experience with Oracle. The database is just too fragile to survive inexperienced people changing it.

> You shouldn't get access to prod if... Well, this was back in the dark ages. Nowadays I'm sold on the notion that we shouldn't be running ad hoc stuff against the live prod database at all. I'm sure there are exceptions, but I'll bet there are more temptations than solid reasons to do so!

Well, somebody has to have access to live prod. That somebody should avoid using it too.

Anyway, everybody should run their queries on a prod-like environment before running on prod, but on Oracle that's really not enough. Also the places that use expensive DBMSes tend not to have a lot of non-prod environments for people to test their scripts.

Nowadays Oracle supports you engineering your data so some of it is not on the bottleneck of anything and you can give some low amount of access to inexperienced people. But that's not the default situation.

Re: An UPDATE without a WHERE, or something close to it

#86

Post by filomeno »

jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

What I usually do is start typing something like "aupdate" or "pdate" (which are wrong) and only come back at the beginning (it's just a ctrl+a) to fix the command when I'm sure the sentence is OK.

Of course if the update is not only important but critical, manually starting a transaction before is the first step ;)

Re: An UPDATE without a WHERE, or something close to it

#87

Post by renewiltord »

jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

DataGrip will not execute it without a LIMIT.

Re: An UPDATE without a WHERE, or something close to it

#88

Post by JeffRosenberg »

jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

I try to start any UPDATE or DELETE by writing out a transaction first:

    BEGIN TRAN;
    
    -- TODO: update statement
    
    ROLLBACK;
Usually I'll run it with the ROLLBACK first, to confirm that it impacts the number of rows I'd expect, and only then change my ROLLBACK to a COMMIT.

Re: An UPDATE without a WHERE, or something close to it

#89

Post by stuartd »

JeffRosenberg wrote: post #88
jusssi wrote: post #16

A particularly nasty aspect of SQL UPDATE syntax is the predicate order, i.e. SET is before WHERE. So when you type it in order, there's a dangerous phase you have to go through if you hit enter by accident. Having run an `mkfs.ext3 /dev/sda` (note the missing partition number) by accident, I've learned to start potentially destructive commands by typing first whatever makes that command a comment (# in shell, -- in…

I try to start any UPDATE or DELETE by writing out a transaction first: BEGIN TRAN; -- TODO: update statement ROLLBACK; Usually I'll run it with the ROLLBACK first, to confirm that it impacts the number of rows I'd expect, and only then change my ROLLBACK to a COMMIT.

I learned this technique the first time I actually worked with a real DBA many years ago and have used it ever since: one thing I like about it is that is mitigates the ‘fear factor’ of production changes, as (topically) ‘fear is the mind killer’ and makes mistakes more likely. Then there’s mitigating ‘complacency’ - “I do this all the time” - which can also lead to mistakes.

And yes, before that I had run an UPDATE without a WHERE in production..

Re: An UPDATE without a WHERE, or something close to it

#90

Post by hermanradtke »

masklinn wrote: post #70
colinmcd wrote: post #48

Earlier quoted context omitted.

Predicate order in SQL is pretty dodgy all around and makes it hard to build intuition around SQL queries. In select, the actual SELECT clause (field selection) can usually be conceptualized as “happening” after all other clauses, but of course it comes first in the query. This problem is fixed in query languages like EdgeQL.[0] [0] https://www.edgedb.com/docs/edgeql/commands/update

> In select, the actual SELECT clause (field selection) can usually be conceptualized as “happening” after all other clauses Not quite true, DISTINCT, ORDER BY and TOP/LIMIT happen afterwards.

Also, HAVING
Post reply on HN