Conditional actions

[hessodreamy]

I need to keep a running count of actions for particular dates, so I need to say 'if this date & value aren't in the table, insert them. if they are, increment them'. Is this possible to do in 1 query? I can't figure how.

Here's what I'm doing at the minute:

SELECT count(*) FROM tracking WHERE date = current_date() AND source = 'somesource' AND target = 'sometarget'

if the result is 0, then insert it:

INSERT INTO tracking (`source`,`target`, `hits`, `date`) 
VALUES ('somesource', 'sometarget', 1, current_date())

otherwise increment:

UPDATE tracking set hits = hits + 1 WHERE `source` = 'somesource' AND `target`='sometarget' AND date = current_date()

This seems pretty inefficient to me. Can it be done in one query? btw i'm using version 4.0

 

[r937]

consider using INSERT ... ON DUPLICATE KEY UPDATE ... syntax

r937.com | rudy.ca

 

[hessodreamy]

That's the trick. I made a unique key out of concatenating other fields, then used the following:

INSERT INTO tracking VALUES(concat(substring('somesource',1,15),'_',substring('sometarget',1,15),'_',substring(current_date(),1,10)), 'somesource','sometarget',1, current_date()) ON DUPLICATE KEY UPDATE hits = hits + 1

DIdn't think OK DUPLICATE KEY was available in the version I Was running, but there you go.