HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Increment value in row if it exists, otherwise create row with value?

Submitted by: @import:stackexchange-dba··
0
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

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 as

CREATE 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.