'MySQL: Insert record if not exists in table [duplicate]

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?

mysql

Solution 1:^[1]

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  |
+----+---------+-----------+------+

Solution 2:^[2]

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)

Solution 3:^[3]

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');

Solution 4:^[4]

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).

Solution 5:^[5]

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

Solution 6:^[6]

You can easily use the following way :

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

In this way, you can insert any new raw and if you have duplicate data, replace a specific column ( The best columns are 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';

Solution 7:^[7]

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'

Solution 8:^[8]

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;

Solution 9:^[9]

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.

Solution 10:^[10]

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

Solution 11:^[11]

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 '[email protected]', '[email protected]') AS tmp 
  WHERE NOT EXISTS (
    SELECT `email` FROM `addr` WHERE `email` LIKE '[email protected]'
  );

Error
SQL query: Copy Documentation

MySQL said: Documentation

#1060 - Duplicate column name '[email protected]'

In the contrary, the query like the one from Mahbub Tito's answer works fine:

INSERT INTO `addr` (`email`, `name`) 
  SELECT '[email protected]', '[email protected]' 
  WHERE NOT EXISTS (
    SELECT `email` FROM `addr` WHERE `email` LIKE '[email protected]'
  );

1 row inserted.

Tested in MariaDB

Solution 12:^[12]

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 )

Solution 13:^[13]

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

Solution 14:^[14]

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,

Solution 15:^[15]

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;

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);

Solution 16:^[16]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	SherylHohman
Solution 2
Solution 3
Solution 4	sdesvergez
Solution 5	Montaser El-sawy
Solution 6
Solution 7	Mohit Gupta
Solution 8	Brian Hooper
Solution 9	dakab
Solution 10	Sgoettschkes
Solution 11
Solution 12	Greg the Incredulous
Solution 13	Puru
Solution 14	Hovhannes Babayan
Solution 15
Solution 16	Andrew____Pls_Support_Ukraine

'MySQL: Insert record if not exists in table [duplicate]

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]

Solution 6:[6]

Solution 7:[7]

Solution 8:[8]

Solution 9:[9]

Solution 10:[10]

Solution 11:[11]

Solution 12:[12]

Solution 13:[13]

Solution 14:[14]

Solution 15:[15]

Solution 16:[16]