Do you have a question? Post it now! No Registration Necessary. Now with pictures!
July 31, 2006, 11:30 am
rate this thread
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
What does this error mean, and how do i rectify it?
Re: MySQL UPDATE QUERY
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.)
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum