MySQL UPDATE QUERY

[wesley@ispace.co.za]

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?

Thanks Wes

[Bill Karwin]

Re: MySQL UPDATE QUERY

wesley@ispace.c o.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.