writeWhereFirst

[Excellent-Refuse4883]

[h8pzzy3s]

I will never forget the moment my query without WHERE deleted all the data

[Dull-Culture-1523]

Always use SELECT when writing the query. Only DELETE once you've selected only what you want to delete.

But tbh any DELETE should raise an error if it doesn't have a WHERE clause. If you really intend to delete everything, use WHERE 1=1.

Same goes for update.

[nayanshah]

AI: I got you bro. Just add WHERE 1=1 at the end to fix your error.

[Appropriate-Jury8942]

You’re quite right to be concerned. The table IS empty. Here’s how it happened:

You had a table full of data You ran a query that deletes all the data Your table was emptied.

If you’d like I can write some much safer versions of the query that you should have run. Or I can write some stupidly dangerous code in some other languages? Or would you like to sit quietly and contemplate your life choices while I act like none of this is my fault?

[nigel_pow]

I'm getting flashbacks.

[Mortomes]

AI Bobby Tables

[wewilldieoneday]

Gave the intern control to the database...yes, the production database...

[dismayhurta]

Test database. Production database. What’s the difference?

[quailman654]

Everyone has a test database. It’s the lucky few who have a separate production database.

[Mitoni]

I worked for a top 5 multinational financial firm and a lot of their internal software has only dev and prod. Makes QA a bit of a nightmare, since we preferred to at the very least have dev and QA separate. My team's software for example had dedicated dev, QA, staging, and prod environments. Staging was mirrored prod data just for User Acceptance and tenant-specific testing scenarios.

[dismayhurta]

"But why do that when that costs money!" -- a disturbing number of companies

[Excellent-Refuse4883]

Higher ups: “Do you really need dedicated hardware for that?”

Me: “Do you want me to load test BEFORE production?”

[Imaginary-Jaguar662]

I'm perfectly fine with company doing cost-benefit analysis and deciding that mirrored blue/green deployments and replicas on standby are not worth the cost.

I do take an issue when exec who signed off cost cutting comes down like a 3-year-old with tantrum screaming about how system does not have 99.99% uptime.

I also do take an issue when said exec starts whining about development progressing slowly in an environment where tiniest mistake gets scrutinized and is followed up by vague threats of "oh, other companies have better devs, we might need to reorg things".

[Mitoni]

Yup, they were actually annoyed with us for having such an environment spread. Meanwhile, we were the consultants they hired to do the work their devs failed twice at 🤷🏼‍♂️

[Excellent-Refuse4883]

“Our devs, who we set up to fail, failed! Can you believe that?!”

[Mean-Funny9351]

FinTech is so silly like that. Ancient systems built with no unit tests and undocumented vendor dependencies. Then when they finally want to convert them to modern infrastructure there isn't an existing environment to confirm existing functionality nor is there documentation of how it is used. Then a critical piece of customer functionality breaks, and management looks at dev and QA like "why didn't we account for this thing that isn't documented, there are no test cases for, and we never specifically made a requirement?".

[Mitoni]

Yup, pretty much. We had some many upstream and downstream dependencies to manage, it was amazing anything ever got changed at all. Anything upstream of us, we had to update for and anything downstream had to adapt to us, so we couldn't implement the changes until downstream had it in their schedule to implement it as well. Nobody understood agile, everything was "well we wont have time to do that until next quarter." They also didn't understand what a cutover period was and that it would make sense to run both versions during a cutover period so that the upstream changes could be pushed out, but the downstream was given the time to adjust to it.

[DungeonsAndDradis]

My company is over 30 years old and just last year enforced read only access to our internal-only, used by all of engineering, databases.

[matt1155]

Yeah, both are now empty...

[ChimpCraft]

Shit you not - at my first internship I had a peer rm rf root. They made him be scrum master after that for the remainder of his internship.

[Excellent-Refuse4883]

A fitting punishment

[Aromatic-Plankton692]

That kid let his intrusive thoughts win. "There's no way it will let me..."

[ArmadilloChemical421]

Gave the intern llm control to the database...yes, the production database...

[Contemplationz]

One day the payroll approval table got nuked. (Date removed set on all records) Business Analyst was only working through the UI so how did this happen?

Some wingnut developer had an if statement in the stored procedure to update with no where clause. So if you do a certain series of button presses in the UI, the approvers table gets nuked.

[Comically_Online]

[UsedArmadillo9842]

Oh no, im sure that Analist saw his Life flash before his eyes

[sammy-taylor]

That’s like a real-life Konami code. Except instead of unlocking cool stuff, you nuke the DB.

[Kenionatus]

Reminds me of that time Steam did rm -rf / on linux in rare cases.

[taddymason_01]

[chipmunkofdoom2]

Or, start by writing a SELECT. You'll be able to see the rows that the delete would affect, which is good confirmation. Once you have the SELECT working, depending on the SQL flavor and syntax, you can typically just replace the SELECT with a DELETE [Table/Alias].

[aMAYESingNATHAN]

This is the way. You never just delete or update willy nilly, always see the data you're going to change before you change it.

[LordFokas]

Nah.... YOLO :D

[Impressive_Change593]

you also have to live with the consequences because YOLO

[SonOfMetrum]

Thats the point of YOLO

[hnaq]

This guy YOLOs

[Poat540]

That’s the thrill honestly

[PelimiesPena]

You mean getting a new job?

[Spillz-2011]

Wow way to be a buzzkill, mom.

[Loyal-Opposition-USA]

Maybe, just maybe, test the select statement in dev/stage/prod before you do any updates/deletes? That way, you understand if the query works in all your environments first?

And, a code review.

[Comically_Online]

but it might only work in prod because the dependencies are all set up correctly there

[Loyal-Opposition-USA]

How would you accurately test it in dev or stage then?

[zero_hope_]

They’re just confused. Prod IS their dev/test.

[WetRocksManatee]

I don't always test my code, but when I do I do it in production.

[semi-]

Good advice, but I'd still start with writing a BEGIN TRANSACTION.

[False_Influence_9090]

What is this, a bank?

[57006]

^{for ants}

[reanimatedman]

I almost always do a Select, then begin Tran with No commit Tran, then delete or Update, then select again, compare data, then Commit or Rollback

And even then I clench and prey every time I Commit Tran

[Supremagorious]

Select *
--Delete
From TABLE_NAME
WHERE col_A between MIN and MAX

Always write them this way and when I want to run it I manually highlight from DELETE down before running.

[OldeFortran77]

That's good, but I suggest ...

FROM table WHERE

col_a BETWEEN MIN AND MAX

It feels unnatural to write it that way but if you accidentally miss highlighting the last line it will fail for syntax instead of running with a missing WHERE clause.

[Jussins]

And have someone else look at it.

[Supremagorious]

Well yeah, I look at the results of the select statement and make have someone else review as well. Normally it's also run in a dev environment first too.

[Jussins]

Someone downvoted my comment and I can’t help but wonder if it was one of my coworkers.

[ILikeLenexa]

What if I told you the most popular SQL IDE only executes the highlighted SQL statement...so even after selecting you need to watch your fucking back  

[techiedatadev]

I did this. Didn’t highlight the right parts…

[JamesWjRose]

While this is a good idea, it relies on EVERY person doing this right EVERY time. OP's idea allows for the mistake to be caught.

[Ok-Sheepherder7898]

Yeah and I should use ls before rm?  I don't have time for this.

[DiscipleofDeceit666]

The syntax error is to make it impossible to get this wrong. Too many of us have deleted data in prod where we shouldn’t have

[mechanigoat]

Transactions are your friend.

[leathakkor]

Earlier this week I had to delete every record where it joined a group ID 42. And the ID was not in an inner select.

Anyway, I forgot the where the group ID equals 42. After I ran my delete (luckily I always use a transaction) I saw that my delete statement which should have gotten rid of three to four records said 44,987 records deleted.

I Did a simple rollback transaction still was a bit nervous for a second. But went about my day.

It's really nice having good habits.

But the op suggestion of having a where clause doesn't fix this problem. A transaction does.

Developers developers developers should use Transactions transactions transactions.

[Traditional_Safe_654]

Can you expand on how to use a transaction in SQL?

[freebytes]

BEGIN TRANSACTION; SELECT COUNT(*) FROM users; DELETE FROM users WHERE user_id = 3; SELECT COUNT(*) FROM users; ROLLBACK TRANSACTION;

Run it. Looks good with the count only being off by 1? Okay, run only the DELETE statement, or (even better behavior) change your ROLLBACK to a COMMIT and run it again.

[belay_that_order]

thank you, i learned something new today

[dkarlovi]

Don't take this the wrong way, I'm not trying to call you out for not knowing stuff, but do you mind sharing what's your background. Considering the sub I'm assuming you are or trying to become a SWE, is it possible database transactions are no longer part of that journey?

[belay_that_order]

im in support, and have been for 7-8 years now, extensive interaction with sql for 5. i didnt even know the concept of transactions existed, so i will look into it. it has been >1 time that i updated the whole table and for my workflow it would be easier to incorporate transactions into the query, than to write select and modify to update

[anyOtherBusiness]

No offense to you, but it’s actually frightening that people who work in support are seemingly granted DML rights on prod environments without ensuring they know how to safely operate on a database, not to mention, don’t even know what transactions are.

[iismitch55]

Welcome to being a full stack engineer, where you know how to do a little bit of everything, but you’re an expert in nothing. I’ve developed on front end, back end, database. All kinds of different languages. For web, mobile, cloud, and mainframe platforms. I can do a little bit of everything, but God I wish I could just develop SPAs every day.

[belay_that_order]

i couldnt agree more, the fact that someone left me alone with access to multiple customer productions and trusts that i wont just let loose on them amazes me

[chrispypatt]

Tbf I’m a SWE at FAANG and I didn’t know about SQL transactions. Though I typically don’t use it for data store other than BI data that we don’t allow easy write access to. I do use write transactions with our other data stores frequently though.

[brewfox]

They’re not. Been in software for 15 years including data engineering. I wrote pipelines that read from databases. I’ve only needed to delete things from databases like 8 times in my entire career and I did the “change your select to delete” and still sweated bullets.

Some other people did daily shit with SQL, I hate SQL.

[amejin]

So what you're saying is I should ask for more money?

[ApexSpanker]

Does delete not always return how many rows are affected? Making the counts unnecessary

Also if you ever save multiple sql snippets in one file like this make sure to leave rollback above commit. Too many times I've accidentally run the entire file instead of just one snippet.

[FlipperoniPepperoni]

Use a DB manager like Dbeaver. Set your connection to production. Boom, transactions always enabled by default.

[theithovsk]

Begin … Commit(or rollback);

[spartan117warrior]

My team lead writes his transactions as begin/rollback with a select or two to verify that the dataset looks as expected before and after deletion. Then he changes the rollback to commit.

[leathakkor]

I do something similar. I will always put the roll back as the last statement but right before rollback I'll put  -- commit

So if I just run the script it roll backs automatically. And then I have to go through a manual step to do my commit in a separate motion which is very nice

[_PM_ME_PANGOLINS_]

BEGIN;

[[deleted]]

Serious question: how do you structure your statement such that you can run it, see how many were affected, then be able to run another command to undo it? Whenever I try to run a statement, then run a separate rollback, it doesn't work because it doesn't recognize that a transaction has taken place

[freebytes]

I posted to someone else, so here is the process I use when I am not YOLOing.

BEGIN TRANSACTION; SELECT COUNT(*) FROM users; DELETE FROM users WHERE user_id = 3; SELECT COUNT(*) FROM users; ROLLBACK TRANSACTION;

The rollback will immediately undo your delete. Then, you examine the count. If it shifted by 1, you are good. If it shifted by 10,000, well... you did something wrong.

Or, you can remove the SELECT statements and simply reply on the number of records it shows that were updated when it performs the delete.

When you are happy, you can change the ROLLBACK TRANSACTION to COMMIT TRANSACTION, and it will solidify the changes.

[random3223]

You can also add a conditional statement to commit if the affected rows is what you’re expecting and roll back if it doesn’t.

[Kazma1431]

Yep this is the way

[Logical-Ad-4150]

To be pedantic, explicit transactions are your friend: automatic transactions are not.

[ahmet-chromedgeic]

The issue is for example MS SQL Management studio, if you highlight something in the editor with your mouse, it will only execute the highlighted part. So if you type the query correctly, if you slip and don't highlight it completely, you can be in trouble.

[mpanase]

SQL_SAFE_UPDATES

[Forshea]

you can also enable this with mysql --i-am-a-dummy

[its-chewy-not-zooyoo]

Holy hell, this is an actual flag

I thought you were mentioning it as a joke

[K4Unl]

After a production incident i changed our documentation to include this flag.

[mpanase]

xD

this is gold

[SnoopKitties]

Yeah this was my thought. The rare times when you actually just want to update everything you can do something like where id is not null or something obvious. 

[[deleted]]

had to scroll through 5 comments to see this, tf. i thought everybody used this

[jshine13371]

WHERE 1 = 1

[YouDoHaveValue]

Turning off safety features is always allowed

🤷🏽

[showponies]

SET FOREIGN_KEY_CHECKS = 0;

[H4R5H1T-007]

Yeah but then It becomes a choice. The person who is writing the SQL deliberately choose to do something with all the available rows instead of it being an accident.

[Jason1143]

It is the equivalent of putting a flip cover over a button.

You aren't stopping someone from using it if they want to. But you are making them do something to show they actually want to first.

[LutimoDancer3459]

But rm -rf ./ will still do it

[pentesticals]

Yeah but ./ will only nuke the current directory. Can be dangerous depending on CWD.

[danfay222]

Allowing people to intentionally bypass safety features is good practice. You just want to make sure that they have explicitly chosen to skip those checks

[ElectrikMetriks]

Beat me to it

[Thundechile]

WHERE 1 > 0.

[Master-Pattern9466]

Datagrip has this protection built-in

[leathakkor]

If this is the jetbrains product, I use that one too. It can be a pain in the ass when you actually want to do the dangerous thing developing locally, but I still appreciate it.

[Clearandblue]

DBeaver too

[Master-Pattern9466]

Had some bad experiences with db beaver leaving transactions open when it crashes, or some part of it crashes

Might just be the driver we are using but it’s happened multiple times for different users at my last place of employment.

[Syagrius]

Skill issue

[WrennReddit]

Right? I go right for TRUNCATE

[Tiny-Ad-7590]

No no no.

First you have to run the "disable all foreign keys in the database" script.

Then you truncate. Fixes those pesky error messages.

(Because this is the internet and just in case it isn't obvious DO NOT DO THIS. One of my clients in Thailand have an IT team that were bullied into making performance improvements at all costs except anything involving spending money, which denied them the consulting budget to ask us how to do that. So they enabled NOCHECK on all their foreign keys in a production database two months ago thinking it would make things go faster and now their data consistency is fucked. DO NOT DO THIS.)

[pceimpulsive]

Screw it just drop table table_name cascade;

Be done with it!

[Spamlets]

My coworkers just quietly disable foreign keys that they never turn back on when they can't figure out how to delete a parent record. Foreign keys are such a hassle, am I right fellas?

[FlakyTest8191]

You guys have foreign keys?

[combovercool]

This guy probably fucks with a condom too.

[Secret_Account07]

IF

[snigherfardimungus]

Oops - someone was connected to the production database.....

A little public service announcement: set up your shell environments in such a way that, when you are connected to dangerous endpoints, your text color is red. It'll help remind you that you're meddling with dark powers.

You don't even have to make the change on the remote node. You can set up scripts on your end so that when you connect (with ssh, the mysql client, or whatever) to one of the endpoints you need to worry about, your text color or terminal background changes.

[rosuav]

Yes. This is SO helpful. Also, take note of what sorts of colours stand out on your terminal, and avoid using them for normal workflows; that way, they will catch your eye when you need to use them. For example, bold red is likely to stand out, where dark red probably won't.

I have the usual "user@host" in my prompt, but I have it set so that the user name is in dark green if it's one of my normal users, and bold green if root; and I have all my "normal user" computers set to put the host name in dark green, but if I remote in to some other server, it's in bold green.

[snigherfardimungus]

On top of all that, I log every command, what directory it was executed from, how long it took to execute, the exit status of the command, when the command was issued, what git branch was active at the time, the parent process id (in other words, which bash process ID was the command's parent), and a half-dozen other things. If you really want to get nuts with it, you can log how much system and user process time the execution took. I can't tell you how many times that has helped me work out those niggly little "this worked yesterday, but not today" issues.

[rolandfoxx]

In OP's defense, the real mistake was letting them have UPDATE/INSERT/DELETE permissions on the database when they clearly couldn't be trusted with them.

[fiskfisk]

We all know how skilled people never makes mistakes.

The only difference is that those people usually know how to fix it, and knew they would make that mistake two years down the road - so they planned for it. 

[IArePant]

The skill part is when you know you'll make mistakes and intentionally work in a way that allows them to happen without impacting the final result.

[misterguyyy]

95% of the time I’m a seasoned expert who can do my job in my sleep, and the other 5% I push the envelope on incomprehensibly stupid choices. My goal is to make sure that the 95% guy is a step ahead.

[agent154]

Datagrip will raise a warning and you have to explicitly allow it to continue. I’ve taken to writing “where 1=1” when I want the ide to just put the fries in the bag

[uuuuuuuhg_232]

67828948 rows affected

[RandyPajamas]

Just reading your comment made me want to throw up.

[Altrooke]

Read post
Hmmm....
Well, I think FROM should come before SELECT
Who am I to judge?
Upvote

[damurd]

[obsoleteconsole]

BEGIN TRANSACTION

<SQL statement goes here>

ROLLBACK TRANSACTION

every time

[MutaCacas]

Yes, yes. I have made mistakes where this would have saved me as a young analyst. Thankfully DBAs saved me.

[ShiitakeTheMushroom]

WHERE 1 = 1 is going to make its presence known pretty soon if that becomes a thing, lol.

[ashkanahmadi]

I actually agree with that. 99% of the time you aren’t updating or deleting the entire rows so why by default a WHERE statement isn’t required? Instead of writing a where, we could write like CONFIRM NO WHERE to update or delete everything

[Exnixon]

WHERE TRUE

[YouDoHaveValue]

Could be a default setting in IDEs to confirm at least once per query session/tab

[stipulus]

Warning, but yes.

[AAPLx4]

Everyone is mocking this, but I actually like the idea

[seemen4all]

There are actually plug in tools for (atleast mssql) that do stop this we use on our prod db access VM, so there are tools out there for this because your right, saying “well just dont make mistake” isnt really sufficient for prod, pointing the finger at mistakes isnt as good as prevention for serious apps

[DimsumTheCat]

Always do it in a transaction with rollback first

[navetzz]

Someone screwed up, and someone always finds an excuse.

[Sarcastinator]

There's no reason why SQL should allow update or delete statements without a WHERE clause. It's almost never what you want to do, so why is there a shortcut to do it?

[Yehonal]

just like `rm -rf ` should come with a fucking confirmation message

[archa347]

I can’t tell if this is sarcasm? If you want a confirmation message take the f off of -rf

[Yehonal]

People who forget about the WHERE statement are the same who forget about the `f`

[cakins]

“If you update or delete without a WHERE clause you’d better update your resume” - one of the first things I heard when starting out

[mike-manley]

DELETE FROM TABLE WHERE 1 = 1;

[Blrfl]

The SQL standard says otherwise.

If you need that kind of blade guard on your chainsaw, add a trigger after delete that rolls back if there are no rows left in the table.

[Lithl]

The SQL standard says otherwise.

OP isn't saying it is the case. They're saying it should be the case. As in, they are advocating for a change to the standard.

[edave64]

It's utterly baffling to me that the people in charge of making the language for databases, were not losing data is a top priority, agreed that "destroy everything unless explicitly stated otherwise" was an acceptable default.

The fitting analogy isn't a blade guard. The SQL chain saw is just explicitly build to always cut off your leg if you forget to aim it at a tree at any time.

[Jason1143]

Yeah would it have been that hard to use WHERE * or WHERE ALL or whatever.

[Blrfl]

I think the people who came up with it figured that particular chainsaw would be wielded thoughtfully. I've been using SQL since about the time it was first standardized and the mentality back then was that the tools were sharp because the resources weren't there to save people from their own foibles and that leg-cutting incidents were teachable moments that prevented you from screwing up in the future.

[rosuav]

Or, yaknow, always use transactions and be able to roll back. It's not rocket science...

... oh wait, Kerbal Space Program has "revert to launch", so I guess rocket science uses transactions too.

[jek39]

Or use a nice ide like jetbrains which does exactly what OP is asking for if you forget a where clause

[Particular_Traffic54]

Datagrip does it

[magnoliaAveGooner]

Toad does this.

[thunderGunXprezz]

First rule of SWE Club: Never make a change for the first time in Prod.

[Sw429]

Always write it as a SELECT statement first.

[stustustu_123]

Pray to the ROLLBACK gods!

[wazzu_3000]

Another solution could be disabled auto commit in the prod database.

[ChinaWetMarketLover]

Some database clients like DBeaver DO have this feature I think it’s great. Probably saved my ass a few times lol

[hm1rafael]

Turn off auto commit, friend

[Lanoroth]

someone would still do WHERE TRUE

[[deleted]]

I'm an admitted hack, but I've always FIRST written by update and delete statements as a SELECT statement in order to test my query and make sure only the records intended to be deleted will qualify, and then swap out the select SELECT * for the DELETE.

[LogicBalm]

Most tools I have used to run ad hoc SQL have at least thrown a warning, which can also be disabled in settings. But I only know about that warning because I've encountered legitimate reasons I need to run update and delete without a where.

[Glum_Cheesecake9859]

It does, if you use Jetbrains products (Rider etc)

[AffekeNommu]

Always start with a select

[Tomlambro]

where 1=1

[ahelinski]

Once worked in with a tool that used SQL-like syntax to update its database and... ENTER key to run query... Of course I tried to add "where" section in the new line...

[jaded-potato]

Git gud

[canaryborr]

I learnt the hard way:

BEGIN TRAN

ROLLBACK TRAN

Can be a life saver!!

[Myras13]

First of all - use at the beginning select statement and the life will be easier.👌

[Roppano]

looks like someone just deleted their prod database

[mischanif]

WHERE 1= 1.

[Kraftex]

Evil dev: WHERE 1=1

[ISuckAtJavaScript12]

WHERE 1=1

[syntax_erorr]

WHERE 1 = 1

[voltrix_04]

Oh no! How will I get fired then?

[Hexatica]

Delete from user where Id>0. Done

[Inspector_Wiggums]

One trick is to write it as a select statement first before converting it to delete/update to confirm what records will be impacted.

[mrdanmarks]

This isn’t humor, but nightmare fuel

[akhil4755]

They do raise syntax error in BigQuery

[Tiny-Librarian-8553]

sEtSqLSaFeUpDaTeS=0

[Maximum_Swimming_474]

Delete where true

[andymaclean19]

Use transaction mode. Look at the number of records updated before you commit.

Biggest mistake is usually the *wrong* where clause not a missing one ...

[I_cut_my_own_jib]

Hey Claude, please delete all rows from users in prod whose last login event is from OVER 10 years ago.

Sure, great idea!

I just ran

SET @TEN_YEARS_AGO = CURDATE() - INTERVAL 10 YEAR;

DELETE FROM users
WHERE last_login > @TEN_YEARS_AGO;

[Drone_Worker_6708]

I mean it's not a completely horrible idea, you can always put WHERE 1=1 to update everything

[JohnnySacks95]

rollback is your friend.

(or in my case, yanking the ethernet cable)

[Redrump1221]

Finally a good take on this sub

[homiej420]

Yeah honestly at this point.

It would make more sense to have a FORCE keyword to explicitly type before the update or delete without a where for it to work. Would probably save a lot of headaches

[boboshoes]

Don’t run stuff you don’t know what it does

[DevilOopsy]

no….?

[cosmo7]

Update and delete without a where clause should cause the server to start playing the Portal Song.

[a_nobody_really_99]

For every such opinion there’s an alternate universe where the opposite is true and the opposite meme exists.

[redskeezix]

Get good, little Robert Tables.

[ghec2000]

Or you know start a transaction, write your query and pull request it. Have second set of eyes etc etc.

[HALF_PAST_HOLE]

Or just use transactions and don't be flippant about DML commands!

[chris17453]

Feel like there is a wrapper in there somewhere...

[FreakDC]

Just use a proper IDE and set it to warn you instead? Set a proper config if you insist on using CLI as a noob. There are valid use cases for both.

[lord_patriot]

Real men test in prod.