ansaurus

Question

Answer 1

+1 A:

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 2010-10-07 04:20:24

Is there no way to do it without a join?

Hailwood 2010-10-07 04:20:57

@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 2010-10-07 04:24:01

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 2010-10-07 04:28:23

@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 2010-10-07 04:30:59

I thought it did a join on every row, so would it not be to the power of?

Hailwood 2010-10-07 04:33:02

@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 2010-10-07 04:35:46

Cheers,Im about to ask another db related question so keep a look out ;)Have an upvote 8D

Hailwood 2010-10-07 04:38:21

ansaurus

tags:

views:

answers:

delete from table1,table2,table3 where?

related questions