SitePoint Sponsor |
|
User Tag List
Results 1 to 6 of 6
-
Jan 15, 2005, 04:23 #1
- Join Date
- Jul 2002
- Posts
- 95
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Error with using the same table in an UPDATE clause
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.
-
Jan 15, 2005, 08:05 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
are you on version 4.1? because you're using a subquery
-
Jan 15, 2005, 08:16 #3
- Join Date
- Jul 2002
- Posts
- 95
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Yes.
It would otherwise tell me to check the syntax near '(SELECT' in the mysql docs (I think).
-
Jan 15, 2005, 08:36 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Jan 15, 2005, 08:59 #5
- Join Date
- Jul 2002
- Posts
- 95
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jan 16, 2005, 10:00 #6
- Join Date
- Jan 2004
- Location
- Uppsala, sverige
- Posts
- 700
- Mentioned
- 2 Post(s)
- Tagged
- 1 Thread(s)
This is a limitation of Mysql, other DBMSs will allow it. And it is standard SQL.
Bookmarks