Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    16

    Unanswered: Conditional actions

    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

  2. #2
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    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',.....

  3. #3
    Join Date
    Mar 2005
    Posts
    16
    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.

  4. #4
    Join Date
    Oct 2004
    Location
    In cousin's house
    Posts
    303
    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

  5. #5
    Join Date
    Mar 2005
    Posts
    16
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •