Error with using the same table in an UPDATE clause

[Rok]

When I try the following query:

UPDATE polls p1
SET p1.votes4 = (p1.votes4 + 1)
WHERE p1.id = (
SELECT MAX(p2.id)
FROM polls p2
);

I get this error:
#1093 - You can't specify target table 'polls' for update in FROM clause



Is it really not possible, to use the same table to get some info? But how can I otherwise find that info, that I need in the UPDATE clause?
I've found a workaround, but it uses two queries.

[r937]

are you on version 4.1? because you're using a subquery

[Rok]

Yes.
It would otherwise tell me to check the syntax near '(SELECT' in the mysql docs (I think).

[r937]

i'm not very experienced in mysql's weird and wacky UPDATE syntax

you might have to use two queries

caution: when you select the max(id) in one query, and before you get a chance to update it in the second query, another row might have been added in the meantime, so you might want to look into setting a transaction lock around the two queries

[Rok]

Yes, I did use 2 queries. But that's a workaround and was wondering, why it can't be done like I tried?

This is for a very small site and such things (another row added in the middle of the UPDATE query) won't happen. But I'm still curious how such a transactional lock can be achieved.

[swampBoogie]

This is a limitation of Mysql, other DBMSs will allow it. And it is standard SQL.