UPDATE AggregatedData SET datenum="734152.979166667",
Timestamp="2010-01-14 23:30:00.000" WHERE datenum="734152.979166667";
It works if the datenum
exists, but I want to insert this data as a new row if the datenum
does not exist.
UPDATE
the datenum is unique but that's not the primary key
Try using this:
If you specify
ON DUPLICATE KEY UPDATE
, and a row is inserted that would cause a duplicate value in aUNIQUE index or
PRIMARY KEY, MySQL performs an [
UPDATE`](http://dev.mysql.com/doc/refman/5.7/en/update.html) of the old row...The
ON DUPLICATE KEY UPDATE
clause can contain multiple column assignments, separated by commas.With
ON DUPLICATE KEY UPDATE
, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify theCLIENT_FOUND_ROWS
flag tomysql_real_connect()
when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values...
I had a situation where I needed to update or insert on a table according to two fields (both foreign keys) on which I couldn't set a UNIQUE constraint (so INSERT ... ON DUPLICATE KEY UPDATE won't work). Here's what I ended up using:
replace into last_recogs (id, hasher_id, hash_id, last_recog)
select l.* from
(select id, hasher_id, hash_id, [new_value] from last_recogs
where hasher_id in (select id from hashers where name=[hasher_name])
and hash_id in (select id from hashes where name=[hash_name])
union
select 0, m.id, h.id, [new_value]
from hashers m cross join hashes h
where m.name=[hasher_name]
and h.name=[hash_name]) l
limit 1;
This example is cribbed from one of my databases, with the input parameters (two names and a number) replaced with [hasher_name], [hash_name], and [new_value]. The nested SELECT...LIMIT 1 pulls the first of either the existing record or a new record (last_recogs.id is an autoincrement primary key) and uses that as the value input into the REPLACE INTO.
Source: Stackoverflow.com