Programming

Oof. Been there, done that, 0 stars; would not recommend.

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.

UPDATE table_name WHERE y = $3 SET w = $1, x = $2, z = $4 RETURNING *
FROM table_name SELECT w, x, y, z

I don't know about the others but oracle has savepoints which allows you to rollback a transaction, so before you do anything big you create a savepoint then when you break it you can roll back

I don't know if it makes you feel better but Tom Scott had a similar experience: https://youtu.be/X6NJkWbM1xk

If it's Microsoft SQL you should be able to replay the transaction log. But you should be doing something like daily full backups and hourly incremental or differential backups to avoid this situation in the first place.

Sh1t already happened, but on the bright side, you learn for the next time. Some tips:

• Make clone of production environment into testing environment. Do stuff there, and after it is tested / verified, do the same in production. Emphasis on "do exactly the same".
• Make regular automatic backup. From time to time, do a backup restore to another computer (VM) and verify that it is restored successfully and is actually usable.
• Poor man's backup is automatic daily (hourly?) snapshots on BTRFS / ZFS / EXT4 filesystems or VolumeShadowCopy on NTFS filesystem.
• Before any UPDATE do a SELECT and verify the data you are modifying are the data you actually want to modify.
• Use a transactions in form of BEGIN TRANSACTION; UPDATE stuff; ROLLBACK; and observe the DB behavior, or see how many rows are affected.
• Somebody has even moar tips, I'm sure. Please write them below.

Unrelated, but use placeholders instead of interpolation right into the query.

See: Little Bobby Tables. https://xkcd.com/327/

Things like this make me glad I can only query my db.

I would run a SELECT with a WHERE clause to see the data I was about to remove to make sure it looked right. Then I would make the delete and Copy and Paste (can you see where this is going? - pun intended) the WHERE clause from the Select into the Delete and run it. But I didn't. This was a production database. It got so hot all of a sudden as I realised what I had done! Lucky we only lost a few hours work.

I am sorry for your loss

SQL scouts credo: I will never use indexes, I will always use column names.

Ctrl+z bro

Jk, sounds tough

One of the reasons I hate working on databases with a passion. I always feel uncomfortable doing anything on DBs.

I‘m using DataGrip (IntelliJ) for any manual SQL tomfoolery. I have been where you are. Luckily for me, the tool asks for additional confirmation when doing any update/delete without where clause.

Also, backups are a must, for all the right reasons and for any project.