MYSQL ERROR 1248 I'm stuck

Question

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'
            )
        )

Answer 1

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.

Answer 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
    )