Hacker News Reader

Comment by yread | Hacker News Reader

For updates, and deletes on the other hand I try to start with the where clause. Learnt the lesson after running a update query once before i typed the where to cause something like 40k worth of damage

View on HN

Good advice. I also break up the statement so that an incomplete highlight and execute will fail, e.g.

    DELETE FROM   
      Customers WHERE    
      Name='Gude' AND   
      Area='Tama'   
      ;

Much harder to leave off half the clause doing this kind of thing.

I've forced the habit upon myself to first compose a SELECT statement and verify that it will give me exactly the dataset that I want to run my operation on. Then I wrap that statement into a CTE and run the DELETE / UPDATE on the CTE.

It doesn't save me time, but a lot of nerves.

I'm used to writing the SELECT query and adding the commented-out DELETE just below the SELECT line.

  SELECT *
  -- DELETE
  FROM Sales
  WHERE Customer = 1

Next, selecting the whole query and executing it. If results are satisfactory - selecting only the DELETE part.

This is the one I use too. Tiny difference - I write

   select * -- delete
   from sales
   where customer = 1

so it's even harder to accidentally highlight the 'delete' part.

Although I've switched to DBeaver for a year now, and it automatically pops up a warning when it detects a DELETE query without a WHERE, which is very nice.

DBeaver has something even better, albeit sometimes annoying: you can define a connection as "Production" (highlighted red) and "Test" (green). On Production systems, DBeaver will ask you for confirmation for every write statement.

Start all your sessions with "BEGIN TRANSACTION".

Then if you make an error you can always ROLLBACK. If it looks OK -- COMMIT.

And then you use MySQL Workbench and forget to turn off autocommmit....

Similar tip: don't start a command from `rm -rf ...`, type `rm /foo/bar/` first only when finished append ` -rf` at the end.

Similarly `git push ... -f` etc.

This saves you only if you delete directories. Better start with `ls`. The one with git force push is spot on though. This is one of the reasons to prefer tools that let you write options at the end of the argument list instead of after the command name.

Start every update and delete with BEGIN; and never fret again.

Yeah i've made a whoops like this before. I usually start a n update with a select statement to make sure my assumptions about the intent of the change is on point.

For destructive statements I almost always do a select with the WHERE statement that I will use in the delete first so I can see I am not doing anything stupid.

Auto-rollback transactions is helpful.

Everything is non-destructive without an explicit COMMIT.

For MySQL, run the command with

--safe-updates

to avoid queries running without WHERE clause.

Reply on news.ycombinator.com