CODEWITHSUNDEEP
phpmysql
Miss Rosy
Miss Rosy

Reputation: 1053

Deleting multiple table once in mysql

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.

Upvotes: 0

Views: 175

Answers (3)

juergen d
juergen d

Reputation: 204756

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

Upvotes: 3

Harshal
Harshal

Reputation: 3622

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.

Upvotes: 0

Lawrence Cherone
Lawrence Cherone

Reputation: 46602

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]

Upvotes: 0

Related Questions