An UPDATE without a WHERE, or something close to it • Hacker News Board

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

#71

Post by BossingAround » Fri, Oct 29, 2021, 7:24 PM UTC

mprovost wrote: post #55

benjaminwai wrote: post #49

Earlier quoted context omitted.

I think !$ takes the last argument on the previous command in history, not previous one on the same line.. at least in bash. For example, if you do: $ ls / $ ls /tmp/junk; rm !$ It's taking "/" rather than "/tmp/junk".. EDIT: formatting

Yes that's right it's the previous command from history. That way you run ls and see if it's the expected output, then rm to delete.

Point being that "ls /path; rm $!" deletes something entirely different than "/path".

What you want is "ls /path; rm $_".

Even then, the above is fairly pointless. At the time you look at what you are deleting, it's gone.

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

#72

Post by markstos » Fri, Oct 29, 2021, 7:26 PM UTC

Ansible playbooks are often written to update all the servers in a group unless you explicitly to tell it to limit it only to a sub-set:

https://docs.ansible.com/ansible/latest/user_guide/intro_pat...

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

#73

Post by BossingAround » Fri, Oct 29, 2021, 7:28 PM UTC

astine wrote: post #44

ts330 wrote: post #43

Surely this will become a non-problem as we migrate to NOSQL... no...? oh? we're migrating back? great.

This is not about SQL; it's about user interfaces. The author is only using SQL as an explanatory tool.

The author is using SQL as an explanatory tool so well I (and at least a few other folks in this thread) have zero idea what they are talking about.

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

#74

Post by blastro » Fri, Oct 29, 2021, 7:31 PM UTC

blakesterz wrote: post #4

Waaay back in the .com boom days of the late 90s I was working at a place where the CTO did something like: UPDATE users SET password = '23r23r23rdsf'; Somehow he missed the "WHERE email = 'someone';" I forget exactly how many users had their password changed that day. For some reason (maybe MySQL didn't let you cancel an UPDATE like that way back in the old days?) to stop that query he ran into the other room and un…

this happened recently at my place of work

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

#75

Post by matsemann » Fri, Oct 29, 2021, 7:33 PM UTC

I made a feature recently that had an interface where one could change some configurations. And other stuff would link to a specific configuration. Mostly a configuration is only used one or two places, but nothing stops someone from reusing it for lots of items, so of course someone do.

When I thought I was done with the page for editing configurations, the UX person said it missed a popup confirming the config changes if it affected more than X items.

I'd prefer skipping it. It's more state to hold, data to be fetched up front etc. But it has probably saved us multiple times already from someone trying to change something used lots of places by accident (instead of making a new separate config for whatever they want to change).

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

#76

Post by matsemann » Fri, Oct 29, 2021, 7:36 PM UTC

dcminter wrote: post #24

magicalhippo wrote: post #20

Earlier quoted context omitted.

I consider any SQL client that doesn't have implicit transactions as broken. Just too easy to make mistakes. However even with that, I agree with your second point. I always type -- commit in my SQL client and then highlight "commit" and execute only the highlighted text. That way I don't commit anything if I accidentally trigger the "execute all" action.

Yes, but mind you I once locked up the database for my colleagues by not realising the consequences of implicit transactions in a DB client. It was a long time ago, but I think it was with SQLPlus on Oracle - and presumably staging or something non-prod since it's not a particularly vivid memory!

I've also often used transactions to play it safe.

But our team learned the hard way that using transactions on the replica pg database actually locks it from getting updates globally during the duration of the transaction. And the whole idea of connecting to the replica was to not wreak havoc, oh well..

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

#77

Post by dcminter » Fri, Oct 29, 2021, 7:36 PM UTC

marcosdumay wrote: post #53

dcminter wrote: post #24

Earlier quoted context omitted.

Yes, but mind you I once locked up the database for my colleagues by not realising the consequences of implicit transactions in a DB client. It was a long time ago, but I think it was with SQLPlus on Oracle - and presumably staging or something non-prod since it's not a particularly vivid memory!

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!

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

#78

Post by witrak » Fri, Oct 29, 2021, 7:42 PM UTC

BossingAround wrote: post #71

mprovost wrote: post #55

Earlier quoted context omitted.

Yes that's right it's the previous command from history. That way you run ls and see if it's the expected output, then rm to delete.

Point being that "ls /path; rm $!" deletes something entirely different than "/path". What you want is "ls /path; rm $_". Even then, the above is fairly pointless. At the time you look at what you are deleting, it's gone.

I think you misunderstood the previous comment. I think that firstly "ls /path" is entered and if the result is ok, then "; rm $_" is added to the copy of the command then executed.

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

#79

Post by matsemann » Fri, Oct 29, 2021, 7:43 PM UTC

marcosdumay wrote: post #53

dcminter wrote: post #24

Earlier quoted context omitted.

Yes, but mind you I once locked up the database for my colleagues by not realising the consequences of implicit transactions in a DB client. It was a long time ago, but I think it was with SQLPlus on Oracle - and presumably staging or something non-prod since it's not a particularly vivid memory!

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.

I once gave thousands of government employees in Norway a long lunch break from what should have been a non-locking migration. Or so I thought. Apparently it was fixed in 12b (or something, a few years ago), and my tests in staging went fine. But in production we didn't run 12b as in staging, but 12a...

Can't remember all the details. But something about adding a new nullable column with or without a default value of some kind. It had to lock the table, but multiple queries were already running. Those queries had some locks already, but then needed those rows the migration had already gotten hold of. Leading to this huge deadlock I had no idea how to solve.

Worst part is, it was one of the first times we tried deploying during working hours. At the time we normally only got to deploy 4 times a year, but we pushed for a more modern approach. Luckily the server guys were on our side, and a few years later that government agency is one of the best technical places I've seen after a complete revamp.

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

#80

Post by Amezarak » Fri, Oct 29, 2021, 7:48 PM UTC

sumtechguy wrote: post #60

SketchySeaBeast wrote: post #11

Earlier quoted context omitted.

Yup. Even if it's just to add a WHERE 1 = 1, at least then you've deliberately loaded and then set off your footgun.

In fact I would go one step further and say why does 'where' default to ALL? You did not say what you wanted. So you should get nothing. This should be true for select and delete as well as well as update. If I just do 'SELECT *' and execute it, it does not rotate through all the tables. I did not specify it. Same with 'SELECT from xyz' if I can not have it empty and return all. Yet where is special somehow.

Because tables and SQL operators are really sets and acting on sets, so naturally operations take place on the set unless a subset is specified. Once you completely and totally internalize this SQL is pretty intuitive, although I think that the clause order might be nicer in a different order.

For example, I would like SELECT to be FROM, WHERE, SELECT. UPDATE could be UPDATE, WHERE, SET. But then DELETE would end up inconsistent...

> If I just do 'SELECT *' and execute it, it does not rotate through all the tables.

Because you did not specify what set you wanted to operate in, and the set of sets isn't meaningful because of schematic differences.

> Same with 'SELECT from xyz' if I can not have it empty and return all.

SELECT is asking what pieces of the subsets (rows) you want to display. If you don't ask for any, you don't get any. You are asking for the number of rows in xyz times zero. That's zero. You can write SELECT 1 FROM xyz and get 1 returned for each subset.