What does this error mean?

[EchoFool]

Hey,

 

I just got a mysql query error but no idea what it means ... my query is:

 

mysql_query("UPDATE user_data SET Proposal = 0 WHERE Proposal NOT IN (SELECT UserID FROM user_data)") or die(mysql_error());

 

Yet its not happy with it as i get this error:

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

 

The idea is if your proposal has the id of a user that is no longer in the database then set proposal to 0.

 

=/

[kickstart]

Hi

 

MySQL doesn't support subqueries in updates where the subquery is selecting from the same table as the update is updating.

 

From the Mysql site (at the bottom):-

 

http://dev.mysql.com/tech-resources/articles/4.1/subqueries.html

 

All the best

 

Keith

[kickstart]

Hi

 

You can you a JOIN in an UPDATE and this might solve the issue you are having.

 

Something like this

 

UPDATE user_data a LEFT OUTER JOIN user_data b ON a.Proposal = b.UserID SET a.Proposal = 0 WHERE b.UserID IS NULL

 

All the best

 

Keith

[gizmola]

Yes, although if kickstart's method doesn't work, you can probably work around the issue using a temp table.  The best way is to use engine=memory. 

 

create table t_orphans engine=memory as SELECT Proposal FROM user_data WHERE Proposal NOT IN (Select distinct UserID FROM user_data);
update user_data u, t_orphans t set Proposal = 0 WHERE u.Proposal = t.Proposal;
drop table t_orphans;