Can I use the same inner join query I use for selecting to delete or are there specifics?

Question

I use this to select all the pictures I need and than I move them with some code.

SELECT estate_pictures.picture
FROM estate_pictures 
INNER JOIN estate 
ON estate_pictures.estate_id=estate.id 
WHERE estate.neighborhood 
IN (56,57,64,67,68,69,71,73,77,81,83,84,85,86,87,95,96,98,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,118,119,120,121,122,123,124,125,127,128,129,130,131,132)

After that I want to delete all the lines that come up in:

SELECT * 
FROM estate_pictures 
INNER JOIN estate 
ON estate_pictures.estate_id=estate.id 
WHERE estate.neighborhood 
IN (56,57,64,67,68,69,71,73,77,81,83,84,85,86,87,95,96,98,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,118,119,120,121,122,123,124,125,127,128,129,130,131,132)

Would just putting "DELETE" instead of "SELECT *" work or is there something more to change for this to work. I'm not clear on how to back up the database yet, so I'd like to be sure it works before I do a DELETE query. Its not like the SELECT ones. I.E. would this work:

DELETE
FROM estate_pictures 
INNER JOIN estate 
ON estate_pictures.estate_id=estate.id 
WHERE estate.neighborhood 
IN (56,57,64,67,68,69,71,73,77,81,83,84,85,86,87,95,96,98,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,118,119,120,121,122,123,124,125,127,128,129,130,131,132)

http://stackoverflow.com/questions/652770/delete-with-join-in-mysql — Natrium, Sep 27 '16 at 11:42

score 0 · Answer 1 · answered Sep 27 '16 at 11:43

0

yes this should work. just use DELETE instead of SELECT * and this should work.

answered Sep 27 '16 at 11:43

Muhammad Talal

78
7

Would this delete from both tables at the same time? I'm not too sure about it after the post from the other guy. – cybera Sep 27 '16 at 12:29
No, this would just delete from the first table. – Muhammad Talal Sep 27 '16 at 19:37

Gordon Linoff · Answer 2 · 2016-09-27T12:33:43.450

You should specify the tables. I am guessing that you want to delete from estate_pictures:

DELETE ep 
FROM estate_pictures ep INNER JOIN
     estate e
ON ep.estate_id = e.id 
WHERE e.neighborhood 
IN (56,57,64,67,68,69,71,73,77,81,83,84,85,86,87,95,96,98,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,118,119,120,121,122,123,124,125,127,128,129,130,131,132);

You can delete from estate as well, but I'm guessing that is not the intention.

And, it is usually a better idea to copy the records you want to delete into a temporary table. Then, use those records to insert into another table and the same temporary table for the deletion. This prevents any problems caused by modifications to the database between the two actions.

EDIT:

To delete from both tables, just include them in the DELETE table list:

DELETE ep, e
FROM estate_pictures ep INNER JOIN
     estate e
ON ep.estate_id = e.id 
WHERE e.neighborhood 
IN (56,57,64,67,68,69,71,73,77,81,83,84,85,86,87,95,96,98,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,118,119,120,121,122,123,124,125,127,128,129,130,131,132);

What do you mean by specify the tables? What are these "ep's" and "e's" you've added? Where do they come from and what do they do? estate_pictures and estate are the names of the tables. The point is infact to delete from both estate_pictures and estate at the same time. How would I move that information to a new table? — cybera, Sep 27 '16 at 12:27
@cybera . . . `e` and `ep` are table aliases. You should learn what they are if you want to use SQL effectively. They are essentially abbreviations for a reference to a table or subquery in the `from` clause. — Gordon Linoff, Sep 27 '16 at 12:34

Can I use the same inner join query I use for selecting to delete or are there specifics?

2 Answers2