-2

Possible Duplicate:
MySQL delete row from multiple tables

I have 5 tables:

  • members
  • member_videos
  • member_photos
  • member_friends
  • member_pages

When I delete that member, I want to delete all his records from these tables. member_id exist in all tables. I'm doing this with 5 queries for each table. Can I use LEFT JOIN or something like this do to this in one query?

Community
  • 1
  • 1
Malixxl
  • 525
  • 2
  • 9
  • 19

3 Answers3

6

Yes, you can.

DELETE m, mv, mp, mf, mpp
FROM members AS m
LEFT JOIN member_videos  AS mv  ON mv.member_id = m.id
LEFT JOIN member_photos  AS mp  ON mp.member_id = m.id
LEFT JOIN member_friends AS mf  ON mf.member_id = m.id
LEFT JOIN member_pages   AS mpp ON mpp.member_id = m.id
WHERE m.id = 12
iblue
  • 27,950
  • 18
  • 84
  • 126
1

http://dev.mysql.com/doc/refman/5.0/en/delete.html

Check this page, there is a section for multiple-tables DELETE.

B F
  • 7,865
  • 13
  • 45
  • 80
-2

I don't think you can. However, I think you can separate the queries with ; and give it as one query to mysql_query() or whatever you're using. For example:

mysql_query('DELETE * FROM members WHERE user_id = 4; DELETE * FROM member_videos WHERE user_id = 4;');

and so on :)

nvlbg
  • 301
  • 1
  • 3
  • 8
  • You want to delete the contents of the `members` table LAST, because of foreign key contraints. – twilson Feb 12 '12 at 15:10
  • I just showed an example, but thanks for the correction :) – nvlbg Feb 12 '12 at 15:11
  • That's not what the OP is asking (multiple statements in one request); it's one query, period. And you can, just [refer to the documentation](http://dev.mysql.com/doc/refman/5.0/en/delete.html). – Jared Farrish Feb 12 '12 at 15:13