Proper Delete in MySQL Using Join

Question

I'm trying to delete data from a table in MySQL, but before I execute the command I want to make sure that I am doing it right. I'm looking to delete all rows with a storeID > 5000000 and cannot match to any item in the deals table. Below is my query:

DELETE FROM stores s 
LEFT JOIN deals sd 
ON s.storeID = sd.store_ID 
WHERE storeID > 5000000 
AND sd.dealID IS NULL;

Will the above query only delete the rows in the stores table and not affect the deals table?

It's a production table, so I don't really want to try without being sure. — user2694306
– user2694306, Commented Feb 24, 2016 at 20:34

Graeme · Accepted Answer · 2016-02-25 19:16:09Z

1

If you are concerned with the query deleting extra stuff from your production table I'd suggest spinning up a development instance and replicating the database schema. If possible take a full dump of the production database and import it into your development/test system. That way you can test your queries and know for certain their outcome.

If you you want a dump of the database or even just the databases schema you can use mysqldump to generate these.

I don't think your query will remove anything from the deals table, but I would recommend you check to be 100% certain. Is this not going to leave you with rows in the deals table that reference a storeid that isn't in the stores table?

edited Feb 25, 2016 at 19:16

answered Feb 24, 2016 at 20:49

Graeme

1,70816 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Proper Delete in MySQL Using Join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related