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" <[EMAIL PROTECTED]>
To: "Jim McAtee" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
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
<[EMAIL PROTECTED]> 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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
