Professional Web Applications Themes

MySql UPDATE problem with duplicate rows - MySQL

Hi, I have a MySql problem I hope someone can help me with. I'm trying to run an update on a linking table, the update is running into a Primary Key constraint violation, and in my workaround I've got stuck trying to write a DELETE statement. Here's the table I'm working on: CREATE TABLE `articles_categories` ( `articleId` int(11) NOT NULL default '0', `categoryId` int(11) NOT NULL default '0', PRIMARY KEY (`articleId`,`categoryId`), CONSTRAINT `articles_categories_ibfk_1` FOREIGN KEY (`articleId`) REFERENCES `articles` (`articleId`), CONSTRAINT `articles_categories_ibfk_2` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ) ENGINE=InnoDB DEFAULT CHT=utf8 ROW_FORMAT=DYNAMIC table: articles_categories - START POINT articleId | categoryId ...

  1. #1

    Default MySql UPDATE problem with duplicate rows

    Hi,

    I have a MySql problem I hope someone can help me with. I'm trying to run
    an update on a linking table, the update is running into a Primary Key
    constraint violation, and in my workaround I've got stuck trying to write a
    DELETE statement.

    Here's the table I'm working on:

    CREATE TABLE `articles_categories` (
    `articleId` int(11) NOT NULL default '0',
    `categoryId` int(11) NOT NULL default '0',
    PRIMARY KEY (`articleId`,`categoryId`),
    CONSTRAINT `articles_categories_ibfk_1` FOREIGN KEY (`articleId`) REFERENCES
    `articles` (`articleId`),
    CONSTRAINT `articles_categories_ibfk_2` FOREIGN KEY (`categoryId`)
    REFERENCES `categories` (`categoryId`)
    ) ENGINE=InnoDB DEFAULT CHT=utf8 ROW_FORMAT=DYNAMIC

    table: articles_categories - START POINT

    articleId | categoryId
    -----------------------
    39 | 7
    39 | 8
    40 | 8

    In my web app I am deleting categoryId=8 from the categories table, so I
    would like to move all the articles that were in that category into
    categoryId=7.

    I cannot just run a simple UPDATE SET categoryId=7 WHERE categoryId=8,
    because that will violate the Primary Key constraint (you can't have *two*
    rows with articleId 39 and categoryId 7). Here's what I want to finish up
    with:

    table: articles_categories - END POINT

    articleId | categoryId
    -----------------------
    39 | 7
    40 | 7

    Here's what I've got so far.

    ******
    STEP 1: copy all rows with categoryId=8 into a temporary table:
    ******

    DROP TEMPORARY TABLE IF EXISTS ac_duplicates;
    CREATE TEMPORARY TABLE ac_duplicates
    SELECT * FROM articles_categories ac WHERE categoryId=8;

    ******
    STEP 2: update all the rows in the duplicate table:
    ******

    UPDATE ac_duplicates SET categoryId=7 WHERE categoryId=8;

    ******
    STEP 3: JOIN the duplicate table to the original table and delete any
    duplicate rows from the original table
    ******

    Here's where I have the problem. I *can* do the join:

    SELECT ac.* FROM articles_categories ac INNER JOIN ac_duplicates acd ON
    acd.articleId=ac.articleId AND acd.categoryId=ac.categoryId;

    But I *can't* work out how to do the DELETE:

    **********
    PROBLEM
    **********

    DELETE FROM articles_categories WHERE articleId IN (SELECT ac.articleId FROM
    articles_categories ac INNER JOIN ac_duplicates acd ON
    acd.articleId=ac.articleId AND acd.categoryId=ac.categoryId)

    All I get is this error:
    Error Code : 1093
    You can't specify target table 'articles_categories' for update in FROM
    clause

    So if someone can help me write that DELETE, I can get what I want, because
    the only remaining step will be very simple: to run my simple UPDATE on the
    original table (no longer violating the Primary Key constraint).

    TIA,

    JON



    Jon Maz Guest

  2. #2

    Default Re: MySql UPDATE problem with duplicate rows

    Jon Maz wrote:
    > Hi,
    >
    > I have a MySql problem I hope someone can help me with. I'm trying to run
    > an update on a linking table, the update is running into a Primary Key
    > constraint violation, and in my workaround I've got stuck trying to write a
    > DELETE statement.
    >
    > Here's the table I'm working on:
    >
    > CREATE TABLE `articles_categories` (
    > `articleId` int(11) NOT NULL default '0',
    > `categoryId` int(11) NOT NULL default '0',
    > PRIMARY KEY (`articleId`,`categoryId`),
    > CONSTRAINT `articles_categories_ibfk_1` FOREIGN KEY (`articleId`) REFERENCES
    > `articles` (`articleId`),
    > CONSTRAINT `articles_categories_ibfk_2` FOREIGN KEY (`categoryId`)
    > REFERENCES `categories` (`categoryId`)
    > ) ENGINE=InnoDB DEFAULT CHT=utf8 ROW_FORMAT=DYNAMIC
    >
    > table: articles_categories - START POINT
    >
    > articleId | categoryId
    > -----------------------
    > 39 | 7
    > 39 | 8
    > 40 | 8
    >
    > In my web app I am deleting categoryId=8 from the categories table, so I
    > would like to move all the articles that were in that category into
    > categoryId=7.
    >
    > I cannot just run a simple UPDATE SET categoryId=7 WHERE categoryId=8,
    > because that will violate the Primary Key constraint (you can't have *two*
    > rows with articleId 39 and categoryId 7). Here's what I want to finish up
    > with:
    >
    > table: articles_categories - END POINT
    >
    > articleId | categoryId
    > -----------------------
    > 39 | 7
    > 40 | 7
    >
    > Here's what I've got so far.
    >
    > ******
    > STEP 1: copy all rows with categoryId=8 into a temporary table:
    > ******
    >
    > DROP TEMPORARY TABLE IF EXISTS ac_duplicates;
    > CREATE TEMPORARY TABLE ac_duplicates
    > SELECT * FROM articles_categories ac WHERE categoryId=8;
    >
    > ******
    > STEP 2: update all the rows in the duplicate table:
    > ******
    >
    > UPDATE ac_duplicates SET categoryId=7 WHERE categoryId=8;
    >
    > ******
    > STEP 3: JOIN the duplicate table to the original table and delete any
    > duplicate rows from the original table
    > ******
    >
    > Here's where I have the problem. I *can* do the join:
    >
    > SELECT ac.* FROM articles_categories ac INNER JOIN ac_duplicates acd ON
    > acd.articleId=ac.articleId AND acd.categoryId=ac.categoryId;
    >
    > But I *can't* work out how to do the DELETE:
    >
    > **********
    > PROBLEM
    > **********
    >
    > DELETE FROM articles_categories WHERE articleId IN (SELECT ac.articleId FROM
    > articles_categories ac INNER JOIN ac_duplicates acd ON
    > acd.articleId=ac.articleId AND acd.categoryId=ac.categoryId)
    >
    > All I get is this error:
    > Error Code : 1093
    > You can't specify target table 'articles_categories' for update in FROM
    > clause
    >
    > So if someone can help me write that DELETE, I can get what I want, because
    > the only remaining step will be very simple: to run my simple UPDATE on the
    > original table (no longer violating the Primary Key constraint).
    >
    > TIA,
    >
    > JON
    >
    >
    >
    No, you can't reference a table you're deleting from in a subselect.

    How about two steps:

    UPDATE article_categories
    SET categoryId=7
    WHERE categoryId=8 AND
    articleId NOT IN (SELECT articleId
    FROM article_categories
    WHERE categoryId = 7);

    Changes any category id from 8 to 7 if there is not already a category of 7 for
    that article.

    DELETE FROM article_categories
    WHERE cateogoryId = 8;

    Deletes the remaining rows.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  3. #3

    Default Re: MySql UPDATE problem with duplicate rows

    Jon Maz wrote:
    > **********
    > PROBLEM
    > **********
    >
    > DELETE FROM articles_categories WHERE articleId IN (SELECT ac.articleId FROM
    > articles_categories ac INNER JOIN ac_duplicates acd ON
    > acd.articleId=ac.articleId AND acd.categoryId=ac.categoryId)
    MySQL supports a (proprietary) syntax for multi-table DELETE statements:

    DELETE FROM ac
    USING articles_categories AS ac
    INNER JOIN ac_duplicates AS acd
    ON acd.articleId=ac.articleId AND acd.categoryId=ac.categoryId;

    This gets around the limitation that MySQL can't SELECT and DELETE from
    the same table in one statement.

    See [url]http://dev.mysql.com/doc/refman/5.0/en/delete.html[/url] for more
    information on multi-table deletes.

    Regards,
    Bill K.
    Bill Karwin Guest

  4. #4

    Default Re: MySql UPDATE problem with duplicate rows

    Hi Jerry,

    Your solution is certainly nice and simple & a big improvement on mine, but
    I just ran into this error:

    UPDATE articles_categories
    SET categoryId=7
    WHERE categoryId=8 AND
    articleId NOT IN (SELECT articleId
    FROM articles_categories
    WHERE categoryId = 7);

    Error Code : 1093
    You can't specify target table 'articles_categories' for update in FROM
    clause

    Any ideas?

    TIA,

    JON





    Jon Maz Guest

  5. #5

    Default Re: MySql UPDATE problem with duplicate rows

    Jon Maz wrote:
    > Hi Jerry,
    >
    > Your solution is certainly nice and simple & a big improvement on mine, but
    > I just ran into this error:
    >
    > UPDATE articles_categories
    > SET categoryId=7
    > WHERE categoryId=8 AND
    > articleId NOT IN (SELECT articleId
    > FROM articles_categories
    > WHERE categoryId = 7);
    >
    > Error Code : 1093
    > You can't specify target table 'articles_categories' for update in FROM
    > clause
    >
    > Any ideas?
    >
    > TIA,
    >
    > JON
    >
    >
    >
    >
    >
    Sorry, that's right. MySQL doesn't allow you to update the table in the
    subselect, either. Sometimes I hate the restrictions in MySQL! (I use DB2 for
    non-web work - much more mature - but much more expensive).

    The only other way I can think of doing this is to temporarily store the info in
    your program then either delete or update, as appropriate. For instance:

    $result = mysql_query('SELECT articleId ' .
    'FROM articles_categories ' .
    "WHERE categoryId = $newcategoryId");
    $list = "";
    while ($data = mysql_fetch_array($result)) {
    if ($list != '')
    $list .= ', ';
    $list .= $data[0];
    }
    mysql_free_result($result);
    $result = mysql_query('DELETE FROM articles_category ' .
    "WHERE article_id IN ($list)";
    $result = mysql_query('UPDATE articles_category ' .
    "SET categoryId = $newcategoryId " .
    "WHERE categoryId = $oldcategoryId");

    Of course, use appropriate error checking.



    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  6. #6

    Default Re: MySql UPDATE problem with duplicate rows

    Hi Jerry,

    Pity, your solution was beautifully simple. I'm gonna try to do all this in
    SQL rather than resorting to php. Perhaps I can add a temporary table to
    your solution and get it to work? Gonna have a play.

    Alternatively there's Bill's suggestion in this thread, but if possible I'd
    like to try to get this working with non-proprietary SQL first.

    Cheers,

    JON


    Jon Maz Guest

  7. #7

    Default Re: MySql UPDATE problem with duplicate rows

    Jon Maz wrote:
    > Hi Jerry,
    >
    > Pity, your solution was beautifully simple. I'm gonna try to do all this in
    > SQL rather than resorting to php. Perhaps I can add a temporary table to
    > your solution and get it to work? Gonna have a play.
    >
    > Alternatively there's Bill's suggestion in this thread, but if possible I'd
    > like to try to get this working with non-proprietary SQL first.
    >
    > Cheers,
    >
    > JON
    >
    >
    Yes, a temporary table just might do it.

    The only other thing I might suggest is to lock the table so no one else can
    update it while you're doing this. Results may not be just what you wish.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  8. #8

    Default Re: MySql UPDATE problem with duplicate rows

    Hi Bill,

    Thanks for your suggestion, it's certainly the most compact solution. But
    in the end I managed to get it done using Jerry's UPDATE then DELETE
    solution with the addition of a temporary table to get round that MySql
    error.

    Cheers,

    JON


    Jon Maz Guest

Similar Threads

  1. Question Remove Duplicate Rows in MySQL Table Having No Primary Key or Unique Index
    By deltaforce in forum Brainstorming Area
    Replies: 0
    Last Post: July 25th, 10:14 AM
  2. How to delete duplicate rows?
    By Clodoaldo Pinto in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: February 4th, 11:11 AM
  3. how to eliminate duplicate rows?
    By dave in forum ASP Database
    Replies: 7
    Last Post: June 29th, 12:19 AM
  4. php & mysql update problem
    By Harald Servat Gelabert in forum PHP Development
    Replies: 2
    Last Post: November 21st, 05:19 PM
  5. Could anyone help me to remove duplicate rows?
    By TaeHo Yoo in forum ASP.NET General
    Replies: 1
    Last Post: July 21st, 07:41 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139