SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    subquery error: "You can't specify target table 'TABLE' for update in FROM clause"

    TABLEA:
    id
    scu

    DELETE FROM TABLEA
    WHERE id NOT IN (
    (
    SELECT MAX(id)
    FROM TABLEA
    GROUP BY scu
    )
    )

    Any ideas why it's throwing that error?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by http://dev.mysql.com/doc/refman/5.0/en/delete.html
    you cannot delete from a table and select from the same table in a subquery
    Create a temporary table from the SELECT query and use that as the condition for your DELETE, then drop the temporary table.

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks.

  4. #4
    SitePoint Wizard chris_fuel's Avatar
    Join Date
    May 2006
    Location
    Ventura, CA
    Posts
    2,750
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    According to a bug report (#5037) there is a workaround that's something like this:

    This works in MYSQL version 5.0.20a-standard

    DELETE FROM TABLE1 WHERE TABLEID IN
    (
    select TABLEID from (SELECT TABLEID FROM TABLE1 WHERE TABLEID = 2689891) AS
    NEWTABLENAME
    );
    Unfortunately I'm not sure how that would compare performance wise against what Dan recommended, and it is somewhat version specific (unless that person only tested it on 5).

  5. #5
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    chris - most helpful - thanks very much


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
  •