Grokbase › Groups › MySQL › mysql › February 2008
FAQ
Hi,

I've got an error message from MySQL 5.0 that I don't understand.

UPDATE "message_revision" SET "HasData" = 1, "Data" = (SELECT "Data"
FROM "message_revision" WHERE "MessageId" = 7 AND "RevisionNumber" = 5)
WHERE "MessageId" = 7 AND "RevisionNumber" = 6

SQL error: [SQLSTATE:HY000, 1093] You can't specify target table
'message_revision' for update in FROM clause

What went wrong?

--
Yves Goergen "LonelyPixel" <[email protected]>
Visit my web laboratory at http://beta.unclassified.de

Search Discussions

  • Baron Schwartz at Feb 5, 2008 at 10:25 pm ⇧
    Hi,
    On Feb 5, 2008 11:26 AM, Yves Goergen wrote:
    Hi,

    I've got an error message from MySQL 5.0 that I don't understand.

    UPDATE "message_revision" SET "HasData" = 1, "Data" = (SELECT "Data"
    FROM "message_revision" WHERE "MessageId" = 7 AND "RevisionNumber" = 5)
    WHERE "MessageId" = 7 AND "RevisionNumber" = 6

    SQL error: [SQLSTATE:HY000, 1093] You can't specify target table
    'message_revision' for update in FROM clause

    What went wrong?
    You can't select from a table you're updating at the same time. What
    "at the same time" means is a bit unclear unless you're one of the
    MySQL developers ;-) However, you can do multi-table updates like
    this:

    UPDATE tbl AS a
    INNER JOIN tbl AS b ON ....
    SET a.col = b.col

    If you absolutely need the subquery, there's a workaround, but it's
    ugly for several reasons, including performance:

    UPDATE tbl SET col = (
    SELECT ... FROM (SELECT.... FROM) AS x);

    The nested subquery in the FROM clause creates an implicit temporary
    table, so it doesn't count as the same table you're updating.
  • Yves Goergen at Feb 5, 2008 at 11:21 pm ⇧

    On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote:
    You can't select from a table you're updating at the same time. What
    "at the same time" means is a bit unclear unless you're one of the
    MySQL developers ;-)
    Yes, Paul DuBois already replied to me off-list. Now I found that
    documentation part and understand that MySQL cannot do this. (Haven't
    tested whether other DBMS can, would be pointless anyway.)

    My goal was to copy some potentially large BLOB from one record to
    another in the same table, nothing more. I have now chosen the way to
    fetch it from the database and have my application just write it back
    again. I wanted to avoid this unnecessary copying around.
    However, you can do multi-table updates like
    this:

    UPDATE tbl AS a
    INNER JOIN tbl AS b ON ....
    SET a.col = b.col
    That sounds interesting, however, I couldn't find it in PostgreSQL's and
    SQLite's reference. Is this a MySQL extension over the SQL standard?

    --
    Yves Goergen "LonelyPixel" <[email protected]>
    Visit my web laboratory at http://beta.unclassified.de
  • Chris at Feb 6, 2008 at 7:15 am ⇧

    Yves Goergen wrote:
    On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote:
    You can't select from a table you're updating at the same time. What
    "at the same time" means is a bit unclear unless you're one of the
    MySQL developers ;-)
    Yes, Paul DuBois already replied to me off-list. Now I found that
    documentation part and understand that MySQL cannot do this. (Haven't
    tested whether other DBMS can, would be pointless anyway.)

    My goal was to copy some potentially large BLOB from one record to
    another in the same table, nothing more. I have now chosen the way to
    fetch it from the database and have my application just write it back
    again. I wanted to avoid this unnecessary copying around.
    Update table set blob2_field=blob1_field;
  • Yves Goergen at Feb 6, 2008 at 9:34 am ⇧

    On 06.02.2008 08:12 CE(S)T, Chris wrote:
    Yves Goergen wrote:
    My goal was to copy some potentially large BLOB from one record to
    another in the same table
    Update table set blob2_field=blob1_field;
    This does something totally different. ;) See my first posting why.

    --
    Yves Goergen "LonelyPixel" <[email protected]>
    Visit my web laboratory at http://beta.unclassified.de
  • Chris at Feb 7, 2008 at 2:55 am ⇧

    Yves Goergen wrote:
    On 06.02.2008 08:12 CE(S)T, Chris wrote:
    Yves Goergen wrote:
    My goal was to copy some potentially large BLOB from one record to
    another in the same table
    Update table set blob2_field=blob1_field;
    This does something totally different. ;) See my first posting why.
    Ah I missed the first post.

    If you don't mind a mysql-specific "fix", and can get the data you want
    from a select query you could:

    insert into table (select goes here) on duplicate key update;

    or maybe a replace into ?
  • Yves Goergen at Feb 7, 2008 at 11:06 am ⇧

    On 07.02.2008 03:52 CE(S)T, Chris wrote:
    If you don't mind a mysql-specific "fix", and can get the data you want
    from a select query you could:

    insert into table (select goes here) on duplicate key update;

    or maybe a replace into ?
    INSERT/REPLACE ... SELECT will always overwrite the entire row, but I
    only want to copy a single column of it. The rest of the record must
    remain intact. So I can't use that, too.

    I also try to avoid DBMS-specific workarounds where I can in this
    project. So maybe one day MySQL will drop the above mentioned
    restriction. :)

    --
    Yves Goergen "LonelyPixel" <[email protected]>
    Visit my web laboratory at http://beta.unclassified.de
  • Jerry Schwartz at Feb 6, 2008 at 3:02 pm ⇧

    -----Original Message-----
    From: Yves Goergen
    Sent: Tuesday, February 05, 2008 6:22 PM
    To: Baron Schwartz
    Cc: [email protected]
    Subject: Re: Error: You can't specify target table '...' for update in
    FROM clause
    On 05.02.2008 23:25 CE(S)T, Baron Schwartz wrote:
    You can't select from a table you're updating at the same time. What
    "at the same time" means is a bit unclear unless you're one of the
    MySQL developers ;-)
    Yes, Paul DuBois already replied to me off-list. Now I found that
    documentation part and understand that MySQL cannot do this. (Haven't
    tested whether other DBMS can, would be pointless anyway.)

    My goal was to copy some potentially large BLOB from one record to
    another in the same table, nothing more. I have now chosen the way to
    fetch it from the database and have my application just write it back
    again. I wanted to avoid this unnecessary copying around.
    [JS] I don't know if this is more efficient that copying the data to a program
    and pushing it back, but you can use a few statements to use a temporary table
    of your own. I often do this so that I can accomplish what I want without
    writing a program at all.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupmysql @
categoriesmysql
postedFeb 5, '08 at 4:26p
activeFeb 7, '08 at 11:06a
posts8
users4
websitemysql.com
irc#mysql

4 users in discussion

Yves Goergen: 4 posts Chris: 2 posts Jerry Schwartz: 1 post Baron Schwartz: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase