469,623 Members | 1,020 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

MySQL UPDATE QUERY

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

Jul 31 '06 #1
1 17378
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.
Jul 31 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by joealey2003 | last post: by
1 post views Thread by Paul | last post: by
10 posts views Thread by frizzle | last post: by
3 posts views Thread by PaulLFC | last post: by
1 post views Thread by whitep8 | last post: by
3 posts views Thread by dgourd | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.