-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
  • 4
    Be verrry, verry careful performing mass-DELETE operations. There's no Ctrl+Z for your MySQL database :) –  Feb 12 '12 at 15:09
  • 1
    You *can* (see [Multiple-table syntax](http://dev.mysql.com/doc/refman/5.0/en/delete.html)), but the better question is, should you? – Jared Farrish Feb 12 '12 at 15:10
  • @JaredFarrish you should post as an answer I'd vote it up. – Toby Allen Feb 12 '12 at 15:12
  • A simple research such as "multiple table delete" would have given you the answer ;) – BMN Feb 12 '12 at 15:13

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
  • 29,609
  • 19
  • 89
  • 128
1

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

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

BMN
  • 8,253
  • 14
  • 48
  • 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
  • 311
  • 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