CODEWITHSUNDEEP
mysqlsql
Ted
Ted

Reputation: 3875

ON DUPLICATE KEY UPDATE... how to update an AUTO INCREMENT?

CREATE TABLE `feeds` (
 `FEED_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `P_ID` bigint(20) NOT NULL,
 `P_NAME` varchar(100) NOT NULL,
 PRIMARY KEY (`P_ID`),
 UNIQUE KEY `FEED_ID` (`FEED_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

::

INSERT INTO `table_a` (`P_ID`,`P_NAME`) VALUES ('30','John') 
ON DUPLICATE KEY UPDATE `P_ID`= VALUES(`P_ID`),`P_NAME`= VALUES(`P_NAME`);

I would like to use INSERT.. ON DUPLICATE KEY UPDATE where anytime I create that insert, I'd like the FEED_ID to auto increment. As for now it stays the same because there was no new row insertion...

Should I delete then insert?

Upvotes: 2

Views: 1827

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

It would seem like you're looking for REPLACE instead of INSERT ... ON DUPLICATE KEY; it will remove the old row if one exists with a conflicting primary key or unique key, and insert a new one with a new FEED_ID;

REPLACE INTO feeds (p_id,p_name) VALUES (1,'Olle');

An SQLfiddle to test with.

Upvotes: 3

Related Questions