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

User · Answer

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.

User · Answer

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

User · Answer

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

[mysql] MySql Table Insert if not exist otherwise update

Examples related to mysql

Examples related to insert