Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

1 Answer 1

up vote 2 down vote accepted
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;
share|improve this answer
    
I'll test it out and let you know. –  Jerry Tunin Mar 5 at 18:37
    
Doesn't quite work as I don't believe there is a unique key. –  Jerry Tunin Mar 6 at 18:36
    
The unique key needs to be (ID,key) for this to work. Sorry, I should have said that earlier. –  RolandoMySQLDBA Mar 6 at 18:37
    
Thank you, worked perfectly. –  Jerry Tunin Mar 6 at 22:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.