On Duplicate Key Update

[BenWeston]

Hi all. I want to log pageviews per day for each profile ID using this:

INSERT INTO pageviews (prof_id,day,views) VALUES ($prof_id,CURDATE(),1) ON DUPLICATE KEY UPDATE views=views+1;

The trouble is, I can't make any of the columns unique as none of them will be so how do I make it update, for example, the following entry:

 

Prof ID: 18     Day: 02-23-2014     Views: 1

 

rather than writing another row with Views: 1 again?

 

[mac_gyver]

you would make the combined prof_id,day columns a composite/unique key.

[BenWeston]

Thanks mac_gyver! The problem now is that running this query:

INSERT INTO `pageviews` (prof_id , day , views) VALUES ('$prof_id' , CURDATE() , 1) ON DUPLICATE KEY UPDATE views = (views+1);

first inserts a new row of 18 - 2014-03-03 - 1 then, when run again, inserts a second row of 18 - 2014-03-03 - 2, then fails to insert any more rows because prof_id and day are indexed and unique. The ON DUPLICATE KEY is looking for all columns to be identical, not just prof_id and day.

Edited March 3, 2014 by BenWeston

[mac_gyver]

if would probably help if you posted your table definition.

[BenWeston]

[mac_gyver]

(re)read what someone suggested -

 

you would make the combined prof_id,day columns a composite/unique key.