MySQL Insert record if not exists in table

Question

I am trying to execute the following query   INSERT INTO table listnames  name  address  tele  VALUES   Rupert    Somewhere    022   WHERE NOT EXISTS       SELECT name FROM table listnames WHERE name  value       But this returns an error  Basically I don t want to insert a record if the  name  field of the record already exists in another record - how to check if the new name is unique

User · Answer

INSERT doesn t allow WHERE in the syntax    What you can do  create a UNIQUE INDEX on the field which should be unique  name   then use either    normal INSERT  and handle the error if the name already exists  INSERT IGNORE  which will fail silently cause a warning  instead of error  if name already exists  INSERT     ON DUPLICATE KEY UPDATE  which will execute the UPDATE at the end if name already exists  see documentation

User · Answer

This is not an answer  it s just a note  The query like the one in the accepted answer does not work if the inserted values are duplicates  like here  INSERT INTO  addr    email    name      SELECT   FROM  SELECT  user domain tld    user domain tld   AS tmp    WHERE NOT EXISTS       SELECT  email  FROM  addr  WHERE  email  LIKE  user domain tld        Error SQL query  Copy Documentation  MySQL said  Documentation   1060 - Duplicate column name  user domain tld   In the contrary  the query like the one from Mahbub Tito s answer works fine  INSERT INTO  addr    email    name      SELECT  user domain tld    user domain tld     WHERE NOT EXISTS       SELECT  email  FROM  addr  WHERE  email  LIKE  user domain tld        1 row inserted   Tested in MariaDB

User · Answer

You can easily use the following way    INSERT INTO     ON DUPLICATE KEY UPDATE       By this way you can insert any new raw and if you have duplicate data  replace specific column   best columns is timestamps    For example    CREATE TABLE IF NOT EXISTS Devices     id         INT 6        NOT NULL AUTO INCREMENT    unique id  VARCHAR 100  NOT NULL PRIMARY KEY    created at VARCHAR 100  NOT NULL    UNIQUE KEY unique id  unique id     UNIQUE KEY id  id      CHARACTER SET utf8   COLLATE utf8 unicode ci   INSERT INTO Devices unique id  time   VALUES   device id     current time    ON DUPLICATE KEY UPDATE time     current time

User · Answer

MySQL provides a very cute solution     REPLACE INTO  table  VALUES  5   John    Doe   SHA1  password       Very easy to use since you have declared a unique primary key  here with value 5

User · Answer

This query can be used in PHP code   I have an ID column in this table  so I need check for duplication for all columns except this ID column    need to change values SET  goodsType   1   sybType 5   deviceId   asdf12345SDFasdf2345        INSERT INTO  devices    goodsTypeId    goodsId    deviceId    need to change tablename and columnsnames SELECT   FROM  SELECT  goodsType   sybType   deviceId  AS tmp WHERE NOT EXISTS       SELECT  goodsTypeId  FROM  devices   need to change tablename and columns names     WHERE  goodsTypeId     goodsType         AND  goodsId     sybType         AND  deviceId     deviceId   LIMIT 1    and now new item will be added only in case of there is not exist row with values configured in SET string

User · Answer

insert into customer keyskill customerID  keySkillID  select  2 1 from dual where not exists        select  customerID  from customer keyskill      where customerID   2      and keySkillID   1

User · Answer

Brian Hooper   You almost hit the point but you have an error in your synatx  Your insert will never work  I tested on my database and here is the right answer   INSERT INTO podatki  datum ura meritev1 meritev1 sunki impulzi1 meritev2 meritev2 sunki impulzi2              SELECT   datum     ura     meritve1    sunki1    impulzi1    meritve2    sunki2    impulzi2                  FROM dual                 WHERE NOT EXISTS  SELECT datum ura                                       FROM podatki                                       WHERE datum   datum  and ura   ura    I m giving you my example of y table  Insert is almost the same like Bian Hooper wrote  except that I put the select FROM DUAL ont from other table   Cind regards  Ivan

User · Answer

INSERT IGNORE INTO  mytable  SET  field0     2    field1    12345   field2    12678    Here the mysql query  that insert records if not exist and will ignore existing similar records   ----Untested----

User · Answer

I had a problem  and the method Mike advised worked partly  I had an error Dublicate Column name    0   and changed the syntax of your query as this         tQ    INSERT  INTO names  name id  surname id  sum  sum2  sum3 sum4 sum5                   SELECT   name     surname     sum     sum2     sum3    sum4    sum5   FROM DUAL                 WHERE NOT EXISTS                   SELECT sum FROM names WHERE name id     name   AND surname id     surname   LIMIT 1      The problem was with column names  sum3 was equal to sum4 and mysql throwed dublicate column names  and I wrote the code in this syntax and it worked perfectly

User · Answer

I m not actually suggesting that you do this  as the UNIQUE index as suggested by Piskvor and others is a far better way to do it  but you can actually do what you were attempting  CREATE TABLE  table listnames       id  int 11  NOT NULL auto increment     name  varchar 255  NOT NULL     address  varchar 255  NOT NULL     tele  varchar 255  NOT NULL    PRIMARY KEY    id     ENGINE InnoDB   Insert a record  INSERT INTO table listnames  name  address  tele  SELECT   FROM  SELECT  Rupert    Somewhere    022   AS tmp WHERE NOT EXISTS       SELECT name FROM table listnames WHERE name    Rupert    LIMIT 1   Query OK  1 row affected  0 00 sec  Records  1  Duplicates  0  Warnings  0  SELECT   FROM  table listnames     ---- -------- ----------- ------    id   name     address     tele    ---- -------- ----------- ------     1   Rupert   Somewhere   022     ---- -------- ----------- ------   Try to insert the same record again  INSERT INTO table listnames  name  address  tele  SELECT   FROM  SELECT  Rupert    Somewhere    022   AS tmp WHERE NOT EXISTS       SELECT name FROM table listnames WHERE name    Rupert    LIMIT 1   Query OK  0 rows affected  0 00 sec  Records  0  Duplicates  0  Warnings  0   ---- -------- ----------- ------    id   name     address     tele    ---- -------- ----------- ------     1   Rupert   Somewhere   022     ---- -------- ----------- ------   Insert a different record  INSERT INTO table listnames  name  address  tele  SELECT   FROM  SELECT  John    Doe    022   AS tmp WHERE NOT EXISTS       SELECT name FROM table listnames WHERE name    John    LIMIT 1   Query OK  1 row affected  0 00 sec  Records  1  Duplicates  0  Warnings  0  SELECT   FROM  table listnames     ---- -------- ----------- ------    id   name     address     tele    ---- -------- ----------- ------     1   Rupert   Somewhere   022       2   John     Doe         022     ---- -------- ----------- ------   And so on     Update  To prevent  1060 - Duplicate column name error in case two values may equal  you must name the columns of the inner SELECT  INSERT INTO table listnames  name  address  tele  SELECT   FROM  SELECT  Unknown  AS name   Unknown  AS address   022  AS tele  AS tmp WHERE NOT EXISTS       SELECT name FROM table listnames WHERE name    Rupert    LIMIT 1   Query OK  1 row affected  0 00 sec  Records  1  Duplicates  0  Warnings  0  SELECT   FROM  table listnames     ---- --------- ----------- ------    id   name      address     tele    ---- --------- ----------- ------     1   Rupert    Somewhere   022       2   John      Doe         022       3   Unknown   Unknown     022     ---- --------- ----------- ------

User · Answer

If you really can t get a unique index on the table  you could try     INSERT INTO table listnames  name  address  tele      SELECT  Rupert    Somewhere    022          FROM some other table         WHERE NOT EXISTS  SELECT name                               FROM table listnames                               WHERE name  Rupert           LIMIT 1

User · Answer

To overcome a similar problem  I have modified the table to have a unique column  Using your example  on creation I would have something like    name VARCHAR 20   UNIQUE  name    and then use the following query when inserting into it    INSERT IGNORE INTO train set table listnames  Rupert

User · Answer

You are inserting not Updating the result  You can define the name column in primary column or set it is unique

User · Answer

I had a similar problem and I needed to insert multiple if not existing  So from the examples above I came to this combination    it s here just in case somebody would need it   Notice  I had to define name everywhere as MSSQL required it    MySQL works with   too   INSERT INTO names  name  SELECT name FROM     SELECT name   FROM          SELECT  Test 4  as name     AS tmp single   WHERE NOT EXISTS          SELECT name FROM names WHERE name    Test 4        UNION ALL   SELECT name   FROM          SELECT  Test 5  as name     AS tmp single   WHERE NOT EXISTS          SELECT name FROM names WHERE name    Test 5        tmp all    MySQL      CREATE TABLE names         OID int 11  NOT NULL AUTO INCREMENT        name varchar 32  COLLATE utf8 unicode ci NOT NULL        PRIMARY KEY  OID         UNIQUE KEY name UNIQUE  name        ENGINE InnoDB AUTO INCREMENT 1   or  MSSQL      CREATE TABLE  names             OID   INT           IDENTITY  1  1  NOT NULL           name  NVARCHAR  32  NOT NULL          PRIMARY KEY CLUSTERED   OID  ASC             CREATE UNIQUE NONCLUSTERED INDEX  Index Names Name  ON  names   name  ASC

User · Answer

Worked     INSERT INTO users  full name  login  password     SELECT  Mahbub Tito   tito  SHA1  12345   FROM DUAL WHERE NOT EXISTS     SELECT login FROM users WHERE login  tito

User · Answer

This query works well   INSERT INTO  user     username     password        SELECT   FROM  SELECT  ersks   md5   Nepal     AS tmp     WHERE NOT EXISTS  SELECT  username  FROM  user  WHERE  username     ersks       AND  password    md5   Nepal     LIMIT 1   And you can create the table using following query   CREATE TABLE IF NOT EXISTS  user         id  int 11  NOT NULL AUTO INCREMENT       username  varchar 30  NOT NULL       password  varchar 32  NOT NULL       status  tinyint 1  DEFAULT  0       PRIMARY KEY   id     ENGINE InnoDB  DEFAULT CHARSET latin1 AUTO INCREMENT 1     Note  Create table using second query before trying to use first query

[mysql] MySQL: Insert record if not exists in table

Examples related to mysql