Request Update with Select * from the same table
I try to do this work and its not an idea?
Code:
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.
Re: Request Update with Select * from the same table
I do not understand "as"
why you do not declare direct "e" and "de"?
Re: Request Update with Select * from the same table
Its my habit. But even without that it does not work:
You can not specify target table 'e' for update in FROM clause
Re: Request Update with Select * from the same table
That will cause an error like this:
Quote:
#1093 - You can't specify target table 'users' for update in FROM clause
which is normal for mysql
UPDATE Syntax:
Quote:
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:
Code:
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
Re: Request Update with Select * from the same table
Thank you very much, it works as you said
Re: Request Update with Select * from the same table
the "de" and the "as" is not accepted by any sql change it and try again