On Sun, Jul 13, 2003 at 11:50:40AM +0200, Alexander Newald wrote:
>
> I'm looking for a solution to write a sql query that inserts a set of data if the
> data id is not present and otherwise update the set.
We covered some of this in a thread named "Bulk loading data" that
started on July 8th. Two solutions were described in that thread.
> I have a table with id (char(8)) and value (int(8))
>
> To make my problem clear:
>
> There is no set with id = "20030713"
> insert into table values ("20030713","10");
>
> The next set is ("20030713","20") but as a set with 20030713 is already available I
> like to have
> update table set value = value + 20 where id = "20030713"
You need to set your table up with id in a UNIQUE index (if it's the
PRIMARY KEY, that's sufficient), then for each new record, you first do
an update (which will fail if the record DOESN'T exist) and then do an
insert (which will fail if the record DOES exist).
$id="20030713";
$value="10";
$q = "UPDATE table SET value=value+$value WHERE id='$id';";
$q .= "INSERT INTO table (id,value) VALUES ('$id',$value)";
@mysql_query($q);
Or if you want to be fancy (that is, more careful):
$id="20030713";
$value="10";
$q1 = "UPDATE table SET value=value+$value WHERE id='$id'";
$q2 = "INSERT INTO table (id,value) VALUES ('$id',$value)";
if ([EMAIL PROTECTED]($q1))
if ([EMAIL PROTECTED]($q2))
$err="Database failure for $id";
> I tried the "replace" command but I fail to get the "value = value + 20" working.
Ya, that's because REPLACE simply deletes the old record before
inserting the new one.
> Its no solution to simply make the table accept duplicate entries for id and make a
> "sum(value) group by id" because I think it will be a performace problem if I have
> thousends of datasets a day - If I'm wrong let me know.
Well, this is indeed your other option. Make all additions to the table
be INSERTs, never UPDATEs, then when you want to pull data out, just SUM
it for a particular field 'id'.
INSERT INTO table (id,value) VALUES ('20030713',10);
INSERT INTO table (id,value) VALUES ('20030713',20);
INSERT INTO table (id,value) VALUES ('20030714',15);
then
SELECT SUM(value) FROM table WHERE id='20030713';
In that case, index on id but don't make it PRIMARY or UNIQUE.
--
Paul Chvostek <[EMAIL PROTECTED]>
Operations / Abuse / Whatever
it.canada, hosting and development http://www.it.ca/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
