2 replies to this topic

[Orjan]

I don't understand why this MySQL query whould generate error?

delete from wolfs_troop where id in (select t.id from wolfs_troop t, wolfs_x_world x where t.vid=x.vid and t.uid <> x.uid)

for me, I get the error

1093 You can't specify target table 'wolfs_troop' for update in FROM clause

can anyone please explain my error?

[WingedPanther]

Try this:

delete from wolfs_troop where id in (select t.id from wolfs_troop t inner join wolfs_x_world x on (t.vid=x.vid) where t.uid <> x.uid)

You haven't specified how your join is defined, which may be the issue.

[Orjan]

Unfortunately, I get the same error with your code.

when reading the errorcode MySQL documentations i read this:
You cannot use the same table (in this case, table t1) for both the subquery's FROM clause and the update target.

So, my solution will be:
not as effective, but working. (sqlquery is a homemade query-function)

$troop = sqlquery("select t.id as tid from wolfs_troop t, wolfs_x_world x where t.vid=x.vid and t.uid <> x.uid");

while ($tdata = mysql_fetch_array($troop)) {

     sqlquery("delete from wolfs_troop where id=".$tdata['tid']);

}