I accidentally removed the WHERE clause from my SQL query in a personal tool. Every row is now the same. I overwrote 206,000+ rows. I have no backup, I am stupid.
![](https://programming.dev/pictrs/image/8140dda6-9512-4297-ac17-d303638c90a6.png)
"UPDATE table_name SET w = $1, x = $2, z = $4 WHERE y = $3 RETURNING *",
does not do the same as
"UPDATE table_name SET w = $1, x = $2, y = $3, z = $4 RETURNING *",
It's 2 am and my mind blanked out the WHERE, and just wanted the numbers neatly in order of 1234.
idiot.
FML.
You are viewing a single comment
This is about the one thing where SQL is a badly designed language, and you should use a frontend that forces you to write your queries in the order (table, filter, columns) for consistency.
I get mildly mad all the time when writing SQL because I feel like it's upside down
Instead of
where the columns are referenced before they're defined (like what is u.id? Keep reading to find out!)
it should instead be
Now nothing is defined before it's used, and you're less likely to miss your where clause. I usually write the joins first anyway because I know what tables I care about, but don't know which specific things I'll want.
I can't think of any other languages that use things before they're defined except extremely dysfunctional JavaScript.
You might enjoy https://github.com/max-sixty/prql
From the cabinet, get a cup.