0

If all tables I want to delete from have the column gamer_id

can i do a delete from table1, table2, table3 where gamer_id = 123?

or is there a different syntax?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Hailwood
  • 89,623
  • 107
  • 270
  • 423

1 Answers1

2

MySQL supports multi-table deletes:

DELETE t1, t2, t3
  FROM TABLE_1 t1
  JOIN TABLE_2 t2 ON t2.gamer_id = t1.gamer_id
  JOIN TABLE_3 t3 ON t3.gamer_id = t1.gamer_id
 WHERE t1.gamer_id = 123
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Is there no way to do it without a join? – Hailwood Oct 07 '10 at 04:20
  • @Hailwood: I don't see the need--the join will include the rows in each respective table where the `gamer_id` is what you specify. Or is the issue that it's possible the `gamer_id` doesn't exist in one of the three tables? – OMG Ponies Oct 07 '10 at 04:24
  • Its more a performance issue, as there will be approx 80 rows in each table that matches, does that not mean it will do 80^3 joins = 512 000 joins? – Hailwood Oct 07 '10 at 04:28
  • @Hailwood: I read that as 80 rows per table, times 3 = 240 rows. You don't have to use multi-table deletion, and process the deletion a table at a time. – OMG Ponies Oct 07 '10 at 04:30
  • I thought it did a join on every row, so would it not be to the power of? – Hailwood Oct 07 '10 at 04:33
  • @Hailwood: No, it depends on the number of rows associated in the joined tables. If there's only one row in each table with the same `gamer_id`, then the resultset is one row. If one of those tables has two and the rest have one row--the resultset will be two rows. – OMG Ponies Oct 07 '10 at 04:35
  • Cheers, Im about to ask another db related question so keep a look out ;) Have an upvote 8D – Hailwood Oct 07 '10 at 04:38