snippetsqlMinor
Increment value in row if it exists, otherwise create row with value?
Viewed 0 times
incrementcreatewithvalueexistsrowotherwise
Problem
Below is the MySQL table layout that I'm working with. Basically, I'd like to increment views, watchers, and inquiries as they occur through the front end of the website. My question is how would I go about having a new _views row for instance created upon the first visit to ID: 1106 or 1107 with a MySQL insert?
ID key value
----- ----- ------
1104 _reserve 10000
1104 _views 100
1104 _watchers 2
1104 _inquiries 1
1105 _reserve 1500
1106 _reserve 24000
1106 _views 236
1107 _reserve 45300
ID key value
----- ----- ------
1104 _reserve 10000
1104 _views 100
1104 _watchers 2
1104 _inquiries 1
1105 _reserve 1500
1106 _reserve 24000
1106 _views 236
1107 _reserve 45300
Solution
INSERT INTO mytable (ID,`key`,`value`) VALUES (1106,'_views',1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1;and
INSERT INTO mytable (ID,`key`,`value`) VALUES (1107,'_views',1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1;If your table has
value defined asCREATE TABLE mytable
(
...
value INT DEFAULT 1,
...
);then the INSERTs would be
INSERT INTO mytable (ID,`key`) VALUES (1106,'_views')
ON DUPLICATE KEY UPDATE `value` = `value` + 1;and
INSERT INTO mytable (ID,`key`) VALUES (1107,'_views')
ON DUPLICATE KEY UPDATE `value` = `value` + 1;Code Snippets
INSERT INTO mytable (ID,`key`,`value`) VALUES (1106,'_views',1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1;INSERT INTO mytable (ID,`key`,`value`) VALUES (1107,'_views',1)
ON DUPLICATE KEY UPDATE `value` = `value` + 1;CREATE TABLE mytable
(
...
value INT DEFAULT 1,
...
);INSERT INTO mytable (ID,`key`) VALUES (1106,'_views')
ON DUPLICATE KEY UPDATE `value` = `value` + 1;INSERT INTO mytable (ID,`key`) VALUES (1107,'_views')
ON DUPLICATE KEY UPDATE `value` = `value` + 1;Context
StackExchange Database Administrators Q#60211, answer score: 6
Revisions (0)
No revisions yet.