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

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?

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