we****@ispace.co.za wrote:
Hi All, i need to know how to solve the error in the following query:
UPDATE table1 SET StopTime = NOW() WHERE StationId = (SELECT StationId
FROM table1 WHERE StopTime = "0000-00-00 00:00:00" AND Id = (SELECT
MAX(Id) FROM table1 WHERE UserName = "notconnected"))
The ERROR message is :
failed : You can't specify target table 'table1' for update in FROM
clause
What does this error mean, and how do i rectify it?
The meaning is that MySQL has a problem using UPDATE and SELECT on the
same table in the same query.
Here's a alternative, using MySQL's multi-table updates:
UPDATE table1 AS t1
JOIN table1 AS t2 ON t1.StationId = t2.StationId
LEFT JOIN table1 AS t3 ON t2.Id < t3.Id
SET t1.StopTime = NOW()
WHERE t2.StopTime = '0000-00-00 00:00:00'
AND t2.UserName = 'notconnected'
AND t3.id IS NULL;
(I did not test that update. Please test it on a copy of your data and
make sure it does what you want.)
Regards,
Bill K.