Question about INSERT on DUPLICATE KEY

Can someone point out what I’m doing wrong in this query? If a person clicks on a link that does on insert to the marketing table, I only want to increase the clicks column if a row already exists for that memberID.

The query below allows another row to be inserted, when it should be updating the clicks column. Also, there is a column called marketingID which is set to auto_increment with each new row that is added.

Thanks!

INSERT INTO marketing 
	(memberID, summitID, clicks, dateClicked)
VALUES 
	('717', '61', '1', '2017-02-23 22:11:37')
ON DUPLICATE KEY
UPDATE
	clicks = values(clicks) + 1;

please do a SHOW CREATE TABLE so we can see the indexes

Here ya go:

CREATE TABLE `notifications` (
  `notificationID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `memberID` int(6) unsigned NOT NULL DEFAULT '0',
  `mailingID` int(6) unsigned NOT NULL DEFAULT '0',
  `dateAdded` datetime NOT NULL,
  `type` varchar(27) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `priority` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `sender` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recipient` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `subject` varchar(70) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `content` mediumtext COLLATE utf8mb4_unicode_ci,
  `dateScheduled` datetime DEFAULT NULL,
  `failedAttempts` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `lastAttempt` datetime DEFAULT NULL,
  `error` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `errorMessage` varchar(256) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dateSent` datetime DEFAULT NULL,
  `timeStamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`notificationID`,`memberID`),
  KEY `notificationID` (`notificationID`) USING BTREE,
  KEY `memberID` (`memberID`) USING BTREE,
  KEY `priority` (`priority`) USING BTREE,
  KEY `dateSent` (`dateSent`) USING BTREE,
  KEY `dateScheduled` (`dateScheduled`) USING BTREE,
  KEY `lastAttempt` (`lastAttempt`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=9765255 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

it appears you have not defined a UNIQUE key upon which the ON DUPLICATE KEY would operate

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.