Deleting multiple table once in mysql

Question

Hi i am trying to delete multiple table row from single query.

I am trying using

 $query="Delete from itineraries where itineraries_id='".$_REQUEST['id']."'
         Delete from itineraries_destination where itineraries_id='".$_REQUEST['id']."'
         Delete from itineraries_photo where itineraries_id='".$_REQUEST['id']."'";

But it's not working. Please Suggest me.

possible duplicate of [Mysql - delete from multiple tables with one query](http://stackoverflow.com/questions/4839905/mysql-delete-from-multiple-tables-with-one-query) — Lawrence Cherone, Aug 21 '12 at 07:36
I have done it My Query is $query = "Delete itineraries, itineraries_destination, itineraries_photo from itineraries INNER JOIN itineraries_destination INNER JOIN itineraries_photo where itineraries.itineraries_id = '".$_REQUEST['id']."' And itineraries_destination.itineraries_id = '".$_REQUEST['id']."' And itineraries_photo.itineraries_id = '".$_REQUEST['id']."'"; Thanks all for replying — Miss Rosy, Aug 21 '12 at 07:47

juergen d · Accepted Answer · 2012-08-21T07:43:39.597

3

try

Delete i, d, p
from itineraries i
inner join itineraries_destination d on d.itineraries_id = i.itineraries_id
inner join itineraries_photo p on p.itineraries_id = i.itineraries_id
where i.itineraries_id = $_REQUEST['id']

SQLFIddle

edited Aug 21 '12 at 07:43

answered Aug 21 '12 at 07:30

juergen d

201,996
37
293
362

1

*mumbles something about injection* – Konerak Aug 21 '12 at 07:31
@MissRosy: you just saying 'not working' does not allow us to help you. Can you copy-paste what error MySQL gives you when you execute this query? – Konerak Aug 21 '12 at 07:36
Thanks..I did it $query = "Delete itineraries, itineraries_destination, itineraries_photo from itineraries INNER JOIN itineraries_destination INNER JOIN itineraries_photo where itineraries.itineraries_id = '".$_REQUEST['id']."' And itineraries_destination.itineraries_id = '".$_REQUEST['id']."' And itineraries_photo.itineraries_id = '".$_REQUEST['id']."'"; – Miss Rosy Aug 21 '12 at 07:46
Like Konerak hinted: You should not just use user input in your query. It allows SQL injections and makes your code very vulnerable. – juergen d Aug 21 '12 at 07:48

score 0 · Answer 2 · answered Aug 21 '12 at 07:33

0

Its all in the manual DELETE:

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

answered Aug 21 '12 at 07:33

Lawrence Cherone

46,049
7
62
106

I have done it My Query is $query = "Delete itineraries, itineraries_destination, itineraries_photo from itineraries INNER JOIN itineraries_destination INNER JOIN itineraries_photo where itineraries.itineraries_id = '".$_REQUEST['id']."' And itineraries_destination.itineraries_id = '".$_REQUEST['id']."' And itineraries_photo.itineraries_id = '".$_REQUEST['id']."'"; Thanks all for replying – Miss Rosy Aug 21 '12 at 07:49

score 0 · Answer 3 · answered Aug 21 '12 at 07:34

0

1) You can only delete from one table with a single delete statement

2) The keyword FROM was missing - eg.

delete from test12 where ...

If you need to delete from mulitple tables, then is it possible to use a foreign key constraint with on delete cascade?

Failing that, I guess you are going to have to write a PL/SQL function to do the job.

answered Aug 21 '12 at 07:34

Harshal

3,562
9
36
65

1

Wrong. You can delete from multiple tables in one statement in MySQL. Checkout the [MySQL Manual, 13.2.2. DELETE Syntax](http://dev.mysql.com/doc/refman/5.6/en/delete.html): `Multiple-table syntax:` – Konerak Aug 21 '12 at 07:37
but koneral for table references we should have foreign key between the tables. – Harshal Aug 21 '12 at 07:43

Deleting multiple table once in mysql

3 Answers3