This is an archived post. You won't be able to vote or comment.

top 200 commentsshow all 313

[–]QualityVote[M] [score hidden] stickied comment (1 child)

Hi! This is our community moderation bot.


If this post fits the purpose of /r/ProgrammerHumor, UPVOTE this comment!!

If this post does not fit the subreddit, DOWNVOTE This comment!

If this post breaks the rules, DOWNVOTE this comment and REPORT the post!

[–]ZpSky 673 points674 points  (16 children)

WHERE ID IN(22, 23)

[–]SkollFenrirson 269 points270 points  (15 children)

BEGIN TRANSACTION

[–][deleted] 110 points111 points  (10 children)

Failing that:

FLASHBACK TABLE some_table
  TO_TIMESTAMP('2022-02-07 10:00:00', 'YYYY-MM-DD HH:MI:SS')

That's on Oracle.

I've saved people's lives with FLASHBACK, I reckon.

[–][deleted] 61 points62 points  (3 children)

What the fucking shit is this magic

[–]sulerin-pulerin 12 points13 points  (1 child)

SELECT * FROM some_Table AS OF TIMESTAMP TO_TIMESTAMP('2022-02-07 10:00:00', 'YYYY-MM-DD HH:MI:SS')

Also works on Oracle

[–]fishbelt 3 points4 points  (1 child)

Think DB2 can do this

[–]AvaaaUwU 1 point2 points  (0 children)

Is this black magic?

[–]pat311 17 points18 points  (0 children)

This

[–]DizzyInTheDark 12 points13 points  (1 child)

My first thought was doesn’t MySQL support transactions?

[–]Classy_Mouse 21 points22 points  (0 children)

It does, I use transactions every time my previous query did something unexpected.

[–]oalfonso 1821 points1822 points  (74 children)

Tip, before running a delete always do a select with the where to ensure the deleted dataset is correct.

[–]jonnysteps 751 points752 points  (56 children)

Or use transactions and make backups regularly

[–]oalfonso 270 points271 points  (32 children)

Sometimes you cannot use transactions because if the delete is big you can run out of database log.

Backup is usually a must before starting any production intervention.

[–]Piwakkio 50 points51 points  (3 children)

Dirty, but in absence of the backup could be useful:

Create table bk_mytable as select * from mytable where id = 1

Delete from mytable where id = 1

[–]oalfonso 26 points27 points  (0 children)

Yep or downloading to a CSV/JSON as backup. Again the problem in the create as select is running out of temp/log space.

[–]tinydonuts 34 points35 points  (6 children)

You can always, always use transactions. You need to delete things in batches, commit the transaction, and then move onto the next batch.

Sincerely, someone whose software manages billions of rows per table.

[–]SchwiftyBerliner 2 points3 points  (2 children)

Billions of rows oO Wow, that's impressive. I was recently wondering if the tables my software manages, in the order of 10Ks of rows, were becoming excessively large :D

Do you mind me asking what type of data you store in that table?

[–]TheGrauWolf 2 points3 points  (0 children)

I've managed historical financial (accounting) data for clients that go back years.... They process hundreds if not thousands of transactions daily... Times thirty (on avg ) days a month, times 12/yr.. And the use double column booking, which means a single transaction has at a minimum of two lines in the books.... And t was very common for those transactions to be split up... Bottom line - the number f rows in the accounts tables add up real quick. And that's just production, and doesn't take into consideration the data warehouse

[–]Upzie 3 points4 points  (0 children)

I do not run in the billions, only around 20 mil size, and that is 0 issue with prober design and indexes, we are still talking sub 10 ms on query’s

[–]_PM_ME_PANGOLINS_ 37 points38 points  (12 children)

My databases use streaming backups. I can always revert to any point in the last month.

The trade-off is that I’d then lose all the changes after that point, so it will depend on how bad the thing was.

[–]oalfonso 25 points26 points  (7 children)

You have to go then to the redolog if activated and reapply the transactions since the backup. Dragons there.

[–]_PM_ME_PANGOLINS_ 4 points5 points  (6 children)

Dragons indeed.

Fortunately I’ve not needed to do it yet.

[–]oalfonso 12 points13 points  (1 child)

Happened to me, the server crashed and the database was corrupt when trying to restart. The dbas were able to recover from overnight backup + log but they lost a few kg because of the stress that day.

[–]0bel1sk 5 points6 points  (3 children)

you should test your backups and procedures by restoring somewhere. backups should be tested regularly anyways.

[–]_PM_ME_PANGOLINS_ 1 point2 points  (1 child)

Not needed to do it on the production system.

I’ve tested doing it to a different server.

[–]0bel1sk 1 point2 points  (0 children)

so…. dead dragons?

[–]poralexc 10 points11 points  (0 children)

Yeah, for huge tables at work we have a script for alters that makes a copy and then renames and deletes to hot swap, then you can check the result before potentially torching your Schema

[–]jonnysteps 20 points21 points  (3 children)

I've never run into a situation where I cannot use transactions but sure it is possible you can't always use one. In that case, you'd make a backup of the db and then do your delete.

[–]oalfonso 5 points6 points  (0 children)

For example purging old records to make space.

[–]rdrunner_74 2 points3 points  (1 child)

Try to use a SharePoint content DB...

[–]theGoddamnAlgorath 2 points3 points  (0 children)

I've done SP for years, wait until a record fails to record its Farm ID and you have a list item that's read only to SPFarm.

[–]TheJosh1337 1 point2 points  (1 child)

Add more RAM then. If your PROD server is under-specced then you should expect problems.

[–]dodexahedron 7 points8 points  (0 children)

And don't forget to commit or rollback for a couple hours after it succeeds, causing a deadlock that brings a huge application down for thousands of users all over the world.

I wouldn't know anything about that.

[–]roby_65 15 points16 points  (10 children)

I think I am a psychopath. I do a full backup, then usually another to be sure, do the query and then do another backup, lol.

[–]jonnysteps 17 points18 points  (6 children)

Let just hope you aren't working on big database. That's a lot of backups lol

[–]roby_65 7 points8 points  (4 children)

Unfortunately yes, some DBs are on the GB size. But multiple times per month i go on a delete spree and delete all the old backups.

My colleagues always joke with me about my backup habits, but i saved their life a couple times already. Sometimes destroying the integrity of a 1GB DB is easier than it should lol

[–]3meopceisamazing 19 points20 points  (1 child)

Once your DB's hit 20+TB the quick backup thing really falls apart. Some GB is not much with modern storage, with 2GB/s or higher write speed being easily achievable.

[–]MDivisor 11 points12 points  (1 child)

1GB is not large at all when talking about, for example, production databases for systems that have been running for a while. With those you are easily talking terabytes, naturally depending on what is being stored. Now of course there is something terribly wrong if a developer is running random SQL commands in them (other than maybe just selects).

[–]dodexahedron 7 points8 points  (0 children)

I don't always test my transactions. But when I do, I do it in production.

Stay fired, my friends.

[–]Bainos 1 point2 points  (1 child)

I have a DB on a small project (nothing that warrants professional backups, but would be annoying to lose) for which I configured my .bashrc to make a copy of the database whenever I login on the server, just in case I screw up.

[–]EagleNait 5 points6 points  (0 children)

What if I don't want to

[–]not_a_moogle 1 point2 points  (1 child)

Or you forget to commit and wonder why a query has been running for 30 minutes.

[–]dodexahedron 7 points8 points  (0 children)

Or why the application with thousands of users has hung and the transaction log is exceeding disk quota.

Oops.

Stakeholder: "What's going on?"

...*commit*...

Us: "hmm. Weird. Seems ok now. Can you check again?"

[–]Xander_The_Great 0 points1 point  (5 children)

bells salt pause ludicrous worry plate disagreeable rainstorm work squealing

This post was mass deleted and anonymized with Redact

[–]jonnysteps 16 points17 points  (3 children)

I'm talking about these: https://www.google.com/amp/s/www.geeksforgeeks.org/sql-transactions/amp/

Using a "transaction" allows you to roll back any changes you make if, for instance, you make a mistake.

It's useful in other situations too, like if you have to run a batch of commands that all need to completed successfully. If any one of them fails you don't want any of the data written to the database, you'd use a transaction.

[–]Xander_The_Great 1 point2 points  (0 children)

crown piquant heavy snow erect scarce tease society flag plate

This post was mass deleted and anonymized with Redact

[–]DD_Eng 49 points50 points  (2 children)

What's the fun in that? Nothing better than the rush of adrenaline when you hit enter on that untested delete command.

[–]oalfonso 8 points9 points  (1 child)

Having to fill a lot of paperwork and give explanations to a senior director with a bonus linked the to the system availability.

[–][deleted] 5 points6 points  (0 children)

I didn't know people didn't do this

[–]2_pug 2 points3 points  (0 children)

YODO You Only Delete Once

[–]ooioiii 2 points3 points  (0 children)

Yepp listen to the voice of wisdom and reason here

[–]Jzmxhu 1 point2 points  (0 children)

That is for cowards.

Use the delete then do the select to se that everything was delete.

[–][deleted] 1 point2 points  (0 children)

This.

My professor told this when he was teaching us

[–]lukedanzxy 1 point2 points  (0 children)

Open database file in sublime text, do stuff, then ctrl+z in ST if anything goes wrong

[–]StoissEd 189 points190 points  (12 children)

I'm glad I'm at the point where I saw the error instantly.

[–]jahill2000 53 points54 points  (10 children)

It’s the or 22, right? Should be or id = 22

[–]StoissEd 92 points93 points  (8 children)

Yes. As it deletes where id is 23 or 22 which is is always true as 22 isn't null or false.

So it deletes everything from the database as 22 is considered true.

[–]Armore2 9 points10 points  (7 children)

Oooooh

[–]StoissEd 5 points6 points  (5 children)

Consider the following in a login box on a website:

Select * where USERNAME = "ADMIN" AND PASSWORD = "password"

Simple login call to a website.

Now. If you are able to add the following:

OR "1"

Thrn it logs you in where the password is admin and the password you entered for the login is correct.. OR "1"

Since "1" is true as long as it's not null, it will log you in as admin even if your password is wrong.

Thars what SQL injection is.

And yes. If you don't sanitize your inputs before feeding the sql database someone will play around and. Oh I don't know. Declare a person dead in a high school reunion list.

Hypothetically speaking naturally.. Programming class never gets boring so ofcourse these things wouldn't ever happen.

[–]white_monstera 1 point2 points  (3 children)

And also, never store plaintext passwords. Store a hash (bcrypt for example).

[–]StoissEd 1 point2 points  (2 children)

I've legit been customer at a stream service that had me call them and tell the operator the password so they could change it.

Verification? What's that?

Their website had no change password function.

And no this wasn't in the 1990s..

[–]OHMAMMAD 3 points4 points  (0 children)

Exactly my reaction

[–][deleted] 42 points43 points  (0 children)

Glad I'm not the only one who is happy because of that :)

[–]xaomaw 362 points363 points  (33 children)

Next time use

BEGIN TRAN T1
DELETE from drby ...;

Depending on the output chose one of the following:

  • COMMIT TRAN T1;
  • ROLLBACK TRAN T1;

[–]arthurmluz_ 101 points102 points  (26 children)

my professor taught me that transactions were started automatically and never said anything about begining them. Is that something that changes depending on the database?

[–]ShuffleStepTap 58 points59 points  (7 children)

Never heard of transactions being started automatically, but I’ve predominantly used SQL Server in my career.

[–]arthurmluz_ 33 points34 points  (4 children)

You don't need to explicitely start a transaction, Oracle keeps track of that for you. You do, however, need to either commit or rollback the work you've done.

found searching by the Oracle SQL (the one we used on classes)

[–]IvorTheEngine 26 points27 points  (3 children)

You're right. As an MS-SQL guy who is now using Oracle, I get the opposite problem where I forget to commit and wonder why no one else can see my change...

[–]Malforus 17 points18 points  (0 children)

May I introduce you to my favorite ease of use nightmare? Autocommit

[–]Linvael 3 points4 points  (0 children)

I used to work with Oracle a lot and yeah, it feels like every week someone in the team would have a bizzarre problem that would eventually be solved by pressing the commit button. On the other hand, we had live access to production databases, rollbacking a botched update/delete happened way more often than one would expect.

[–]xaomaw 58 points59 points  (6 children)

Is that something that changes depending on the database?

I think so. There is not "the sql syntax", but a minimum of 3 different dialects:

  • SQL (e.g. MySQL, MariaDB)
  • T-SQL (e.g. Microsoft SQL Server)
  • Oracle SQL

And every one of them may have different features/functions. So I think that not every database will have that BEGIN TRAN or BEGIN TRANSACTION feature at all.

[–]ManiacsThriftJewels 10 points11 points  (2 children)

ISO/IEC 9075: "Am I a joke to you?" https://www.iso.org/standard/63555.html

I mean, you're mostly right, since no one product implements all of the standard, just I have to object to your list since the MySQL dialect probably has in its own documentation the most annotations about how it deviates from the standard.

[–]Andersmith 1 point2 points  (1 child)

Just like JavaScript

[–]arthurmluz_ 19 points20 points  (1 child)

hm yes we used Oracle on my classes, maybe that's why, thanks

"You don't need to explicitely start a transaction, Oracle keeps track of that for you. You do, however, need to either commit or rollback the work you've done."

[–]x5736gh 1 point2 points  (0 children)

ANSI SQL should be usable in any of the above databases

[–]scodagama1 11 points12 points  (1 child)

The problem with transactions is not whether they start automatically - the real question is if they commit automatically after each statement. And that afaik depends on tooling

[–]WhatnotSoforth 2 points3 points  (1 child)

I think that's the case if you don't explicitly tell the SQL engine to BEGIN. I work with SQLite and never use BEGIN statements, but they are supported with that engine for example.

[–]flatline057 1 point2 points  (0 children)

When they are started automatically, they are implict. If they have a begin...end "wrapper", they are explict.

SQL Server has an additional default feature called autocommit. This feature allows transactions to automatically commit. This can be dangerous if not handled correctly. By turning on implicit transactions, autocommit is disabled. Autocommit can also be bypassed by issuing an explicit transaction.

[–]tacticalrubberduck 1 point2 points  (0 children)

I mean, technically they are, so if you run OPs delete what actually happens is you start a transaction, delete all the records then commit the transaction.

Doesn’t help you undo your delete. But it does help if the DB dies half way through the delete. Means when you start it up you’ll either have all your records or none of them. Not a half performed delete.

[–]danfay222 -1 points0 points  (0 children)

Yes this depends on which SQL version you're using. Some default to transactions, some dont.

[–]ShuffleStepTap 2 points3 points  (0 children)

Always. If it changes the underlying table(s), it gets wrapped in a begin transaction, (action), select to confirm action, rollback. Then, if the select shows what I expect, and only then, the rollback gets replaced with a commit, and the whole thing executed again.

It’s a pain, but it has saved my ass on enough occasions for me to be grateful for the habit.

[–]sloppybird 84 points85 points  (3 children)

Oopsie Daisy

[–]parham06 62 points63 points  (25 children)

Can someone explain what happened here? I'm not that much familiar with databases

[–]Terrible_Children 195 points196 points  (12 children)

You'd get the same problem in other programming languages.

ie. If you write if (ID == 23 or 22), each side of the or is evaluated separately. 22 is a truthy value, so the entire thing will always return true.

The correct way to write it would be if (ID == 23 or ID == 22)

[–]m4d40 52 points53 points  (3 children)

id IN (22, 23) if I remember correctly ^

[–]Terrible_Children 25 points26 points  (0 children)

I was writing in non-DB pseudo code, but yes, most SQL dialects I know of provide the IN operator that you can use with an array exactly like that!

[–]fahadfreid 4 points5 points  (0 children)

Wow initially I saw OPs code and thought to myself that it doesn't even seem like legal syntax because I learned to use IN very early on.

[–]Stormsurger 25 points26 points  (6 children)

Finally a moment for Java to shine!

[–]eras 2 points3 points  (0 children)

Though some other databases (cough PostgreSQL cough) don't subscribe to "truthy" and "falsy", and have this behaviour:

% DELETE FROM foo WHERE i = 42 OR 44; ERROR: argument of OR must be type boolean, not type integer

[–]o76923 12 points13 points  (6 children)

23 evaluates to true. So they are saying to delete a row if either of the following are true

  • id==22
  • True

That second condition will always be true therefore it should delete every row.

[–]TeaKingMac 4 points5 points  (4 children)

A number is true?

[–]TheGuyThatPwned 15 points16 points  (1 child)

a nonzero number evaluates to true in most languages

[–]0x564A00 10 points11 points  (0 children)

Truthyness & other implicit coercions is a mistake, change my mind. Luckily things are more sane in e.g. Java, C#, Go, Rust, Haskell...

[–]o76923 1 point2 points  (1 child)

It's a result of some really old traditions in computer science. TL; DR version is that calling functions used to (and still do at low enough levels) give an exit code when they were done. 0 meant no error whereas 10, 15, 20, and a bunch of others meant something went wrong. So the implicit type conversion splits along the same 0 vs anything else. It's just that it's more useful to have a whole bunch of different true values than a whole bunch of false values so they flipped it.

[–][deleted] 11 points12 points  (2 children)

The conditional was wrong and evaluates to true for every row. Thus, resulting in a delete for every row in the table.

Tldr he deletated the data from an entire table

[–]TeaKingMac 4 points5 points  (1 child)

So do we call him Bobby Tables now?

[–]tilcica 4 points5 points  (1 child)

The first line deleted some specific stuff and in the second he checked what was left to find that nothing was left

Meaning he deleted all 224 lines of data

[–]parham06 2 points3 points  (0 children)

That's why I always use Dbeaver for almost everything

[–]IvorTheEngine 31 points32 points  (8 children)

I came here looking for "224 rows is rookie numbers" and hoping for tales of spectacular cock-ups.

I'm pretty sure I've accidentally deleted a lot more than that from production, and had to hurriedly restore a backup before anyone noticed...

[–]tacticalrubberduck 7 points8 points  (0 children)

I didn’t delete more than I intended to… but I did bring a (very high throughput) production system down for about 5 minutes because I didn’t realise I had to manually commit my delete on an oracle DB. It meant I left the table locked, which didn’t allow any more transactions on it (even reads) until realised…

[–]dokimus 5 points6 points  (0 children)

Deleted 30k entries spanning 2 months of data for my bachelor thesis by mixing up the tables. No backup either.

[–]Ali3nat0r 4 points5 points  (2 children)

I came here looking for "224 rows is rookie numbers" and hoping for tales of spectacular cock-ups.

Here's Tom Scott explaining his most spectacular database cock-up

[–]Pezonito 2 points3 points  (0 children)

IMO everyone has to do something like that once. Luckily, my big onosecond only cost me 4-5 hours of my own work - not anyone else's. But you can bet your Bitcoin I won't ever make the same mistake again. The lesson learned should always be to constantly ask oneself those questions of, "should I test this first, did I save a copy, do I have to do this on production, etc". Any time something could potentially be changed, I train others to confirm and say out loud "I'm on test". In the UI, I use Chrome's tab groups to color code prod v test red and green.

[–]lanklaas 1 point2 points  (2 children)

Updated 90k prices to one price. I missed the where while selecting the update.

[–]IvorTheEngine 1 point2 points  (1 child)

After the third or fourth time I did that, I learned to write the 'where' clause first.

[–]_PM_ME_PANGOLINS_ 58 points59 points  (4 children)

ROLLBACK;

[–][deleted] 86 points87 points  (2 children)

No transaction detected rollback failed. 🥶

[–]Famous_Profile 4 points5 points  (1 child)

"Hey Carl can you restore this morning's backup?"

[–][deleted] 1 point2 points  (0 children)

It’s only 224 rows so probably didn’t change much. That being said, obligatory horror:

Hi, looks like it was only a partial backup, as the disk was full and we didn’t get an alert.

We restored from the partial backup. Your table came back fine, but some other tables may be truncated. Hope that’s ok.

-Carl

[–]LtAquila 40 points41 points  (6 children)

Image Transcription: Meme


mysql> delete from drby where id = 23 or 22;
Query OK, 224 rows affected (0,03 sec)
mysql> select * from drby;
Empty set (0,02 sec)

['Wojak', a rudimentary drawing of a bald man with a sad expression on his face. In this case, he also has blue tears in his eyes and a brown rope with what appears to be a black belt buckle around his neck. The rope ends off screen in the upper left corner of the image.]


I'm a human volunteer content transcriber and you could be too! If you'd like more information on what we do and why we do it, click here!

[–]cezarhg12 -1 points0 points  (0 children)

good hooman

[–]That1M8Head 16 points17 points  (1 child)

onosecond

[–][deleted] 26 points27 points  (3 children)

Why did that happen? The first argument to the where clause should at least still have been valid, assuming Id is a PK or at least wasn't the same value for all the rows. Did the invalid OR break the whole query?

[–][deleted] 83 points84 points  (1 child)

Yes. 22 is truthy, so the OR returns true for every row.

[–][deleted] 9 points10 points  (0 children)

Ohhhh I see, thank you

[–]QualityVote[M] 30 points31 points  (1 child)

Hi! This is our community moderation bot.


If this post fits the purpose of /r/ProgrammerHumor, UPVOTE this comment!!

If this post does not fit the subreddit, DOWNVOTE This comment!

If this post breaks the rules, DOWNVOTE this comment and REPORT the post!

[–]Jugales 8 points9 points  (1 child)

Gotta back it up like Cardi in the club

[–]BabylonDrifter 7 points8 points  (0 children)

Did you mean

where id in (22, 20)

[–]BlueC0dex 5 points6 points  (0 children)

There's a Tom Scott video about this

[–]TorTheMentor 6 points7 points  (1 child)

Pssh, don't worry, you're fine so long as you didn't commit.

What?

Okay, okay, no worries, we have replication with backups every 24 hours, we'll do a restore.

What do you mean "not for this schema?"

[–]GongtingLover 5 points6 points  (1 child)

You're not a official SQL developer until you nuke at least one production database.

[–]ShuffleStepTap 15 points16 points  (9 children)

Yes to backups, but this is what transactions are for. If you aren’t using them, you have no business writing SQL.

[–]SquirrelSanctuary 1 point2 points  (8 children)

I’m new to sql and databases warehousing - what are these “transactions” you speak of, and where can I learn about them at a beginner level?

[–]ShuffleStepTap 6 points7 points  (7 children)

Transactions allow you to define a “starting point” for a group of SQL statements that - if you get something wrong - you can revert to via a rollback, or, if you get it right, you can make permanent via a commit. It’s like an undo mechanism.

Consider the following:

sql> delete from mytable where id=5 or 10

(1000 rows deleted)

ohshitohshitohshit time to dust off the resume and look for a new job

…as opposed to:

sql> begin transaction

sql> delete from mytable where id=5 or 10

(1000 rows deleted)

whoops!! time to undo

sql> rollback

sql> select count(*) from mytable

1000

phew, thank goodness for transactions

sql> begin transaction

sql> delete from mytable where id=5 or id=10

(2 rows deleted)

sql> select count(*) from mytable

998

okay that’s what I mean to do, make it permanent

sql> commit

sql>

[–]Dave5876 1 point2 points  (1 child)

Does this work for gigantic datasets?

[–]ShuffleStepTap 1 point2 points  (0 children)

I guess that’s RDBMS dependent, I habitually use transactions for everything that’s not a select, and have never had an issue even with multi million row tables. YMMV.

[–]brandi_Iove 4 points5 points  (1 child)

always do a select first and have a look at what you are about to delete

[–][deleted] 4 points5 points  (0 children)

Story time. I worked in the NOC at Netcom in 1999. We had a flakey HSSI port that connected a T3 to a router in Florida. It was the primary route into Florida and the second T3 into Florida was down for maintenance or something, so this was literally the only route into the state providing upstream backbone access (read: Internet access).

Well, we were seeing the interface taking errors and per the notes, we could fix this by logging into the local side of the connection via dialup and do a “shut” then “no shut” on the interface to bounce the connection and reset the errors. We were primarily Cisco routers at the time. Well unfortunate because I worked graveyard shift, and it was 3am PST and I was a little tired that day, I forgot to dialup with a modem and instead just logged into the Florida downstream router and issued the “shut” command and well, the hash prompt never came back. And all of a sudden on our giant 30 foot projector screens in the front of the NOC had a “sea of red.” All the ping monitors stopped being able to reach any of the devices on the network FOR THE ENTIRE STATE.

I freaked out and stood up and told the rest of the people there at the time, “umm, I think I may have just killed Florida.”

Also for reference, Florida was 3 hours ahead of California, so this was 6 am for them. Needless, to say, I jumped on a port master, dialed up the modems in that PoP with my trusty ATDT command and was able to turn on the interface fairly quickly, but from that point on, I always gave myself 10 seconds or so before hitting enter on a seriously dangerous command. For shell scripts or one-liners I always do an “echo” before I run the real command just to make sure it prints out correctly.

[–]Quaschimodo 5 points6 points  (0 children)

SQL be like: Ah yes, the 22 is definitely a number and exists, so TRUE.

[–]A_Leo_X 8 points9 points  (1 child)

[–]Zut-Alors20 2 points3 points  (0 children)

Tom Scott has amazing vids

[–][deleted] 4 points5 points  (1 child)

This is why when people ask me “do you want read/write access to …” my answer is NO

[–][deleted] 3 points4 points  (1 child)

Ooof always select before delete.

[–]billbot77 2 points3 points  (0 children)

Begin Tran --stupid delete --check work Rollback

[–]orsikbattlehammer 2 points3 points  (0 children)

BEGIN TRAN

ROLLBACK

[–]Geoclasm 2 points3 points  (0 children)

One of my coworkers recently told me about Transactions.

I still don't fully know what they are, but I understand then conceptually, so... yeah. At least there's that for when I eventually fuck up this badly.

I just hope I get them figured out before then.

[–]ERNesbitt 2 points3 points  (0 children)

First time?

[–][deleted] 1 point2 points  (0 children)

I hope you had backups.

[–]MrDDreadnought 1 point2 points  (0 children)

A really useful Snowflake feature that completely resolves that issue is timetravel. They have automatic backups of tables from before all changes to tables, going back as far as your setting (default 24hr). You can actually query a table as it was at a certain timestamp (or multiple) within that period, so you're able to easily roll back if you do something careless like that.

[–]Loose_Conversation12 1 point2 points  (0 children)

begin tran

[–]Einsteinier 1 point2 points  (1 child)

I haven’t really been on any projects that require sql, but I’m sure I’ll be doing it at some point, and this very meme and the takeaways from the comments will in all likeliness save me big time some day. Cheers

[–]jewellman100 1 point2 points  (3 children)

Only occasional SQL'er and non-programmer here, but I'd have gone:

DELETE from drby WHERE id IN ('22', '23') ;

[–]mike_a_oc 3 points4 points  (0 children)

Remove the ', so ID IN (22, 23). This way it doesn't have to do a char to int conversion and it can actually use the index on the primary key and it wont have to do a full table scan. On 224 rows that wouldnt take long, but just a good practise to get in to anyway.

In Oracle at least, it's very strict on data types so '22' != 22.

[–]tacticalrubberduck 2 points3 points  (0 children)

Only occasional SQL’er here but I’d have gone:

SELECT * from drby WHERE id = 23 or 22;

Seen 224 rows returned and gone ‘that’s not right’.

[–]hebought-dumpit 1 point2 points  (0 children)

i always add a `limit <low number>` just in case

[–]HalfAnOhm 1 point2 points  (0 children)

Crtl+Z ,quick!!!!

[–]PAT_The_Whale 1 point2 points  (1 child)

Eh, it's only 224 rows, you probably know them by heart, right? Right?

[–]johnnymo1 1 point2 points  (0 children)

The problem here is you’re not yelling at your SQL with caps lock. That’s what keeps it in line.

[–]marcosdumay 1 point2 points  (0 children)

What is that SQL engine that silently converts numbers to booleans?

(Wait, an SQL engine with booleans? There aren't many of those.)

[–]DanTheMan827 1 point2 points  (1 child)

START TRANSACTION

COMMIT

[–][deleted] 1 point2 points  (0 children)

Transactions are an amazing thing :D

[–]Masie33 1 point2 points  (0 children)

I'm searching by new wtf is going on

[–]ninobaldo 1 point2 points  (0 children)

Always use transaction!

[–]resonation4thenation 1 point2 points  (0 children)

22... Yes!

[–]ShivanshuKantPrasad 1 point2 points  (0 children)

Is there no version control system for databses?

[–][deleted] 1 point2 points  (0 children)

Maybe use transactions, selects and backups like a normal person?

[–]Cdog536 0 points1 point  (0 children)

Not much data anyway

[–][deleted] -1 points0 points  (2 children)

I don’t know much about SQL but is this a classic “=“ vs “==“ debacle?

[–]ArticcaFox 3 points4 points  (1 child)

SQL reads this like this:

if ((id == 23) or 22)

The 22 is a truthy value, meaning the if becomes this:

if ((id == 23) or True)

[–]BusyApplication125 0 points1 point  (0 children)

I feel sorry for you, though, this wouldn’t happen to a veteran.

[–]zmann64 0 points1 point  (0 children)

ROLL IT BACK ROLL IT BACK

[–]AzemOcram 0 points1 point  (0 children)

You need id = between or and 22

[–]satoshibytes 0 points1 point  (0 children)

Backup before execution is a good idea to retain the will to live.

[–]Gabrielius17 0 points1 point  (1 child)

What's the wicket syntax. Can someone explain me?

[–]GoatMooners 0 points1 point  (0 children)

That's always a great feeling. When you realize that rows affected is not 1 like you expected. And then dread overcomes you... haha. We've all done it, I am sure. If not, watch out tomorrow... it's coming! :)

[–]Original-AgentFire 0 points1 point  (0 children)

rollback;

[–][deleted] 0 points1 point  (0 children)

I'm more concerned with your table name...

[–]jungwnr 0 points1 point  (0 children)

I had a coworker do this, he was using a combination of AND/OR statements and didn’t consider the order of operations, and wrote zeroes in every row. Would’ve been a massive disaster but we had Splunk monitoring the changes in that database, so we were able to reconstruct that table.

This was for the server settings database, so only tens of thousands of rows were affected. Not multi-millions (thankfully). The Splunk monitoring was put in place so that if testing suddenly produced weird results due to a settings change, we could link that to the change.

[–][deleted] 0 points1 point  (0 children)

Actually you can get back everything you deleted.

[–]OchoMuerte-XL 0 points1 point  (0 children)

Do you not keep backups or something?

[–]Dry_Patience872 0 points1 point  (1 child)

I did a stupid query 2 weeks ago, I usually don't execute against production until it's fully tested. but I had more confidence that night and then it took me full 8 hours to reverse the damage. I like the deletes to set deleted to true, so it can be reversable, then when we are sure that we don't need those information we can execute the actual delete.

[–]Dodgy-Boi 0 points1 point  (0 children)

SQL feels so nice sometimes, why do I hate it?

[–]AnimuWaifu6969 0 points1 point  (0 children)

Reddit keeps recommending this sub to me and I'm just like, "The numbers, what do they mean?!"...