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 CHARSET=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 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 CHARSET=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.
jstucklex@attglobal.net
==================

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 http://dev.mysql.com/doc/refman/5.0/en/delete.html for more
information on multi-table deletes.

Regards,
Bill K.

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 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.
jstucklex@attglobal.net
==================

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 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.
jstucklex@attglobal.net
==================

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