MySql Table Insert if not exist otherwise update

Question

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

Answer 1

Jai is correct that you should use INSERT ... ON DUPLICATE KEY UPDATE.

Note that you do not need to include datenum in the update clause since it's the unique key, so it should not change. You do need to include all of the other columns from your table. You can use the VALUES() function to make sure the proper values are used when updating the other columns.

Here is your update re-written using the proper INSERT ... ON DUPLICATE KEY UPDATE syntax for MySQL:

INSERT INTO AggregatedData (datenum,Timestamp)
VALUES ("734152.979166667","2010-01-14 23:30:00.000")
ON DUPLICATE KEY UPDATE 
  Timestamp=VALUES(Timestamp)

Answer 2

Try using this:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index orPRIMARY 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 the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values...

Answer 3

This is not too bad, but we could actually combine everything into one query. I found different solutions on the internet. The simplest, but MySQL only solution is this:

INSERT INTO wp_postmeta (post_id, meta_key) 
SELECT 
  ?id, 
  ‘page_title’ 
FROM 
  DUAL 
WHERE 
  NOT EXISTS (
    SELECT 
      meta_id 
    FROM 
      wp_postmeta 
    WHERE 
      post_id = ?id 
      AND meta_key = ‘page_title’
  );
UPDATE 
  wp_postmeta 
SET 
  meta_value = ?page_title 
WHERE 
  post_id = ?id 
  AND meta_key = ‘page_title’;

Link to documentation.

Answer 4

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.