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