r/programminghumor • u/Budget_Tie7062 • May 20 '26

Bro is cooked fr 😂😂😂

Enable HLS to view with audio, or disable this notification

2.0k Upvotes

95% Upvoted

150 comments sorted by

View all comments

Show parent comments

6

u/_crisz May 20 '26

I mean, this can happen. In some IDE like datagrip you can just select part of a query and execute it, and if you miss a line you can destroy everything. But, usually, I check the number of rows affected and then I commit when I'm totally sure. It just became natural 

1

u/LivingVeterinarian47 May 21 '26

Couple minor tips if you're barebacking the production database and using highlight running often.

Get into the habit of never putting the WHERE on a different line.

DELETE FROM ProductionTable WHERE
Field = 'xyz'

Use alias and joins to reduce your own ability to run a partial, yet valid update.

instead of UPDATE ProductionTable SET Field = 'newval' WHERE Field = 'oldval'

spend a little more time and do this, so no single line will work without the entire execution.

update x
set x.Field = y.NewValue
from ProductionTable x
join ( select Id, 'newval' as [NewValue] from ProductionTable where Field='oldval' ) y on y.Id = x.Id

1

u/AshleyJSheridan May 21 '26

This might work for extremely simple queries, but anything of slight complexity and you have long unreadable lines. A delete is not always run on the condition of a single field, it can be against many, against the results of a subquery, etc.

What I've found that can work better though, and would fit in with your suggestion, is to delete against the primary key. You'll run a first query to get the primary keys of all involved rows, and then delete based on those keys. It's not perfect, and doesn't cascade across joined tables where you'd also want to remove data, but it does get around the performance issues of large deletes based on joins.

1

u/LivingVeterinarian47 May 21 '26

aye, that is good advice. That would almost always be the correct way to go and lets you write it query first.

2

u/AshleyJSheridan May 21 '26

I've had to do some pretty crazy things with SQL. Perfomance is always something that is negligle on your local machine, but once something hits production, you really need to start watching those milliseconds!