busboy
February 24, 2017, 10:08pm
1
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;
r937
February 25, 2017, 12:48am
2
please do a SHOW CREATE TABLE so we can see the indexes
busboy
February 25, 2017, 2:33am
3
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
r937
February 25, 2017, 8:25am
4
it appears you have not defined a UNIQUE key upon which the ON DUPLICATE KEY would operate
system
Closed
May 27, 2017, 3:25pm
5
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.