0

Ok, so I'm new to derived tables. When I run this query it gives me a MYSQL ERROR 1248. Anyone know of a work around for this? This is probably the most complex query I have done and I just want to get it to work. Thank you!

delete from table_1
    where thing_id in (
        select tid from(
            select thing_id as tid
            FROM table_1
            inner join table_2
            on table_1.thing_id = table_2.thing_id
            where stuff = 'stuff'
            )
        )
Cody Esmay
  • 11
  • 2
  • 4

2 Answers2

3

MySQL normally does not allow you to reference the table being deleted (or updated) in the rest of the statement. You can get around this using nested subqueries (as you seem to be attempting to do). However, I think it is better to use explicit joins for the update:

delete t1
    from table_1 t1 join
         (select t1.thing_id as tid
          from table_1 t1 inner join
               table_2 t2 
               on t1.thing_id = t2.thing_id
          where stuff = 'stuff'
        ) tt
        on t1.thing_id = tt.tid;

That said, I think this is equivalent to just doing the join on table2:

delete t1
    from table_1 t1 join
         table_2 t2 
         on t1.thing_id = t2.thing_id;
   where stuff = 'stuff';

This latter method should have better performance as well.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Malicious? No. Mischievous? Yes ;-) The first part of this is not relevant to the problem. That said, the second part looks spot on! – Strawberry Nov 19 '14 at 01:05
  • @Strawberry . . . I would say that using unsupported syntax in a database is *very* relevant to a problem. It might not have to do specifically the syntax error, but the OP will fix that and then get another error. It seems malicious to me, if that is the reasoning, because the first solution is the correct re-write of the query (although there could be syntax errors in the specific code). – Gordon Linoff Nov 19 '14 at 01:13
  • "MySQL does not allow you to reference the table being deleted". This statement is true. It's just not relevant to this problem - hence juergen d's solution. I suspect that we have differing views on the nature of malice. – Strawberry Nov 19 '14 at 01:16
  • @Strawberry . . . Are you missing that `table_1` is both the table being deleted *and* it is in the subquery? – Gordon Linoff Nov 19 '14 at 02:50
  • If your understanding of the problem is correct, then Juergen's solution will return an error message, right? But it doesn't, – Strawberry Nov 19 '14 at 08:09
2

You forgot the table alias(es)

delete from table_1
where thing_id in (
    select tid from(
        select table_1.thing_id as tid
        FROM table_1
        inner join table_2
        on table_1.thing_id = table_2.thing_id
        where table_1.stuff = 'stuff'
        ) tmp
    )
Strawberry
  • 33,750
  • 13
  • 40
  • 57
juergen d
  • 201,996
  • 37
  • 293
  • 362