MySQL: Insert record if not exists in table

mysql
MySQL: Insert record if not exists in table MySQL: Insert record if not exists in table - Solution Checker - solutionschecker.com - Find the solution for any programming question. We as a solution checker will focus on finding the fastest possible solution for developers. Main topics like coding, learning.

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?

Solution 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

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

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

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

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

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

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

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

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

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

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

Solution 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

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

Solution 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

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

Solution 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

More Questions

Getting a map() to return a list in Python 3.xWhat are all the common undefined behaviours that a C++ programmer should know about? [closed]How to sum all column values in multi-dimensional array?Get a list of numbers as input from the userJava string to date conversionPassing data between controllers in Angular JS?POST a form array without successfulHow do I read an entire file into a std::string in C++?What's the difference between passing by reference vs. passing by value?A variable modified inside a while loop is not rememberedWhat is the difference between POST and PUT in HTTP?Protocol doesn't conform to itself?Split / Explode a column of dictionaries into separate columns with pandasHow to get the user input in Java?Clean up Excel Interop Objects with IDisposableHow do I print an integer in Assembly Level Programming without printf from the c library?Get element from within an iFrameDo the parentheses after the type name make a difference with new?Error "initializer element is not constant" when trying to initialize variable with constTranspose/Unzip Function (inverse of zip)?