MySql UPDATE problem with duplicate rows

[Jon Maz]

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

[Jerry Stuckle]

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

[Bill Karwin]

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.

[Jon Maz]

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

[Jerry Stuckle]

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

[Jon Maz]

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

[Jerry Stuckle]

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

[Jon Maz]

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