CODEWITHSUNDEEP
mysql
Jared
Jared

Reputation: 113

MySQL: delete containing sub-select failing

I have this statement in MySql:

delete from match_custom_field_team_value
where match_id=10648 
      and match_custom_field_id=14917 
      and event_id in (2, 6, 8, 4)
      and team_id in (select mcfv2.team_id from match_custom_field_team_value mcfv2 
                        where mcfv2.match_id=10648 and mcfv2.match_custom_field_id=14917 and mcfv2.event_id=9);

When I attempt to run this, I get:

Error Code: 1093. You can't specify target table 'match_custom_field_team_value' for update in FROM clause

Any idea why that is throwing an error, and the best way to rewrite to to avoid the error? (I know I could do it with a temp table, but would rather not go the extra step.)

Thanks,

Jared

Upvotes: 0

Views: 60

Answers (2)

Jared
Jared

Reputation: 113

I really appreciate your help, but I have to move on with this, so I rewrote using a temp table

drop table if exists tmp_cfs_to_nuke;
create temporary table tmp_cfs_to_nuke(
    team_id INT(11) unsigned
);

insert into tmp_cfs_to_nuke 
    select mcfv2.team_id from match_custom_field_team_value mcfv2 
        where mcfv2.match_id=10648 and mcfv2.match_custom_field_id=14917 and mcfv2.event_id=9;

delete from match_custom_field_team_value
where match_id=10648 
  and match_custom_field_id=14917 
  and event_id in (2, 6, 8, 4)
  and team_id in (
    select team_id from tmp_cfs_to_nuke
 );

Upvotes: 0

Elon Than
Elon Than

Reputation: 9765

You can not select from table and update/delete from it in one query. It's prohibited, but you can workaround it in dirty way.

delete from match_custom_field_team_value
where match_id=10648 
  and match_custom_field_id=14917 
  and event_id in (2, 6, 8, 4)
  and team_id in (
     select * from (
         select mcfv2.team_id from match_custom_field_team_value mcfv2 
            where mcfv2.match_id=10648 and mcfv2.match_custom_field_id=14917 and mcfv2.event_id=9)
     ) as sub
  )

Thanks to that MySQL will not operate on same table but on results fetched from that table. It should work but it's not good way (think eg. about performace).

Also before using this query read more about possible problems here https://dba.stackexchange.com/questions/1371/problem-with-mysql-subquery (link by @ypercube ).

Upvotes: 1

Related Questions