Conditional actions

hessodreamy · #1 (**permalink**) 10-13-05, 09:23

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:

Code:

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

if the result is 0, then insert it:

Code:

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

otherwise increment:

Code:

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

popskie · #2 (**permalink**) 10-13-05, 23:40

i dont if it is ok in 4.0 cause i use 5.0.13. but try this.
u should make ur 2 fields as primary key. then use this query.

insert into tablename(datefield,field2,field3,...)values('valu es','value','value',...) on duplicate key update field3='values',.....

hessodreamy · #3 (**permalink**) 10-14-05, 04:03

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

Code:

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.

popskie · #4 (**permalink**) 10-14-05, 04:45

ahh i dont understand u.....my question is ,on duplicate key is available in version 4? if yes u dont ned to concatenate the string. the only things to remember is the filtering expression. ex i have table below and its field.

table1
field1 field2 field3 field4 field5
1111 aaaa aaaa vvvv cccccc

if ur query like dis.
update table1 set ................ where field1 = 'any value'
u hve 2 make field1 as primary key but if the query is

update table1 set ................ where field1 = 'any value' and field2 ='dfsdfs'u hve 2 make field1 and field2 as primary key

insert into tableI(feild1,field2,field3,field4,field5) values('fvield1','fvield2','fvield3','fvield4','fv ield5') on duplicate key update field5 =field5+1,blalalahhh

hessodreamy · #5 (**permalink**) 10-14-05, 04:50

the concatenating the string was purely to create a unique key.
There had to be 1 entry unique to a date/source/target combination. I read in the manual that keys on duplicate fields was not advised using ON DUPLICATE KEY UPDATE, so I did a hack job and created a unique field with a combination of the other fields.
As for availability in 4.0, either i'm wrong about that, or i'm wrong about what version i'm using!

Internet Services

Web Development

Digital Marketing

Consumer Tech