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 · Accepted 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

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

[mysql] MySql Table Insert if not exist otherwise update

Examples related to mysql

Examples related to insert