SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2002
    Posts
    95
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    are you on version 4.1? because you're using a subquery
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    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).

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    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.

  6. #6
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •