Request Update with Select * from the same table

[Wguy2008]

I try to do this work and its not an idea?

UPDATE machine_280 as e 
SET e.driver = '0' 
WHERE e.ID = ( SELECT MAX(de.ID) FROM machine_280 as de WHERE de.number='290' AND de.date_time<='2009-02-03 04:12:00' AND de.driver='2' AND de.curr<>'0' );

I'm in MySql.

[Ashok.M]

I do not understand "as"

why you do not declare direct "e" and "de"?

[Wguy2008]

Its my habit. But even without that it does not work:

You can not specify target table 'e' for update in FROM clause

[Lemog]

That will cause an error like this:

#1093 - You can't specify target table 'users' for update in FROM clause

which is normal for mysql

UPDATE Syntax:

Currently, you cannot update a table and select from the same table in a subquery.

if your request is described in a literal way, we obtain the following:
give the value "0" the field "driver" of table "machine_280" when the "ID" field is equal to "ID" the greatest of the table "machine_280" among those who have both the value "290" for the "number", the date less than or equal to '2009-02-03 04:12:00', the value "2" to "driver" and "curr" different from "0"

attention for use as the motion is that the MAX() can return a single value. a priori, the ID field is probably the primary key of the table and is therefore to auto increment should not pose de problem

As against, it follows that when updating a single line of your table (only one ID returned in the second part of the motion, so a single ID in the first part)

If the request is made at the beginning really need a ton, the following should be able to replace:

UPDATE machine_280 SET driver = '0' 
WHERE number='290' AND date_time<='2009-02-03 04:12:00' AND driver='2' AND curr<>'0' 
ORDER BY ID DESC 
LIMIT 1

[Wguy2008]

Thank you very much, it works as you said

[nanakofiboafo]

the "de" and the "as" is not accepted by any sql change it and try again