No help for 3.23, but in 4.1 you could use
INSERT INTO dailycounters (trackingdate, thingid, count)
VALUES (<today>, <somerecordid>, 1)
ON DUPLICATE KEY UPDATE count = count + 1;
assuming a UNIQUE index on the combination of trackingdate and thingid.
Michael
Jim McAtee wrote:
> Ah, I see what you're saying. Each event as it happened might be a
> simple insert into a temporary table and then I could batch the total
> daily activity into a daily record at the end of the day. A classic
> size vs. speed tradeoff.
>
> I was just hoping there might be a (My)SQL way to say "update record if
> found, otherwise create one" in one query.
>
>
> ----- Original Message ----- From: "Eric Bergen" <eric.bergen@stripped>
> To: "Jim McAtee" <jmcatee@stripped>
> Cc: <mysql@stripped>
> Sent: Saturday, December 04, 2004 2:40 PM
> Subject: Re: More efficient way?
>
>
>> Depending on how your application works you might be able to batch the
>> daily updates. Example only do an update every 20 items instead of for
>> each one. Alternatly you could run an update with a join every 10
>> minutes or so that would update the daily counter.
>>
>>
>>
>> On Thu, 2 Dec 2004 20:56:45 -0700, Jim McAtee
>> <jmcatee@stripped> wrote:
>>
>>> I have an application which keeps a table of daily event counters
>>> related
>>> to other records in a databse. Since the trackingrecords are kept on a
>>> daily basis new records are created each day for items being referenced.
>>>
>>> In pseudo-code:
>>>
>>> // Check for the existance of daily tracking record
>>> SELECT dailycounterid
>>> FROM dailycounters
>>> WHERE trackingdate = <today>
>>> AND thingid = <somerecordid>
>>>
>>> if <query.recordwasfound>
>>>
>>> // If it exists, increment counter
>>> UPDATE dailycounters
>>> SET count = count + 1
>>> WHERE dailycounterid = <query.dailycounterid>
>>>
>>> else
>>>
>>> // Otherwise add new record with count of 1
>>> INSERT INTO dailycounters
>>> (trackingdate, thingid, count)
>>> VALUES
>>> (<today>, <somerecordid>, 1)
>>>
>>> endif
>>>
>>> Is there any way to do this with a single MySQL query instead of a check
>>> followed by either an insert or an update? The MySQL version is 3.2x
>>> using MyISAM tables.
>
>
>
