Skip to content
Fix Code Error

MySQL: Insert record if not exists in table

March 13, 2021 by Code Error
Posted By: Anonymous

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

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  |
+----+---------+-----------+------+
Answered By: Anonymous

Related Articles

  • changing value of threshold value when getting error…
  • Getting "requires numeric/complex matrix/vector…
  • How do I include certain conditions in SQL Count
  • How to remove MySQL completely with config and…
  • How can i display data from xml api in flutter?
  • error LNK2005: ✘✘✘ already defined in…
  • SQL query return data from multiple tables
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • Gradle error: Execution failed for task…
  • Should MySQL have its timezone set to UTC?
  • Form field border-radius is not working only on the…
  • org.gradle.api.tasks.TaskExecutionException:…
  • Remix error The transaction has been reverted to the…
  • Howto: Clean a mysql InnoDB storage engine?
  • Jetpack Compose and Hilt Conflict
  • Execution failed for task…
  • How to change the color of vaadin-select-text-field…
  • error: 'Can't connect to local MySQL server through…
  • phpMyAdmin on MySQL 8.0
  • How do I count unique visitors to my site?
  • data.table vs dplyr: can one do something well the…
  • How can I fix MySQL error #1064?
  • Use of Jquery on scroll event
  • ERROR 1698 (28000): Access denied for user…
  • From inside of a Docker container, how do I connect…
  • T-SQL How to create tables dynamically in stored procedures?
  • Are PDO prepared statements sufficient to prevent…
  • Oracle: If Table Exists
  • How can I initialize a MySQL database with schema in…
  • Creating an index on a table variable
  • Django - update inline formset not updating
  • Polymer 1.0 'array-style' path accessors,…
  • MySQL ERROR 1045 (28000): Access denied for user…
  • How does database indexing work?
  • How to set root password to null
  • Database development mistakes made by application developers
  • MySQL server has gone away - in exactly 60 seconds
  • Can not connect to amazon RDS from spring boot container app
  • Ukkonen's suffix tree algorithm in plain English
  • When I'm testing a web app by JUnit and Mockito I…
  • For-each over an array in JavaScript
  • Cookie blocked/not saved in IFRAME in Internet Explorer
  • SQL syntax error when executing MySQL script using…
  • Multiple items from DropDownList with protected sheet
  • How to import XML file into MySQL database table…
  • How to change the MySQL root account password on CentOS7?
  • VueJS: Cannot read property 'name' of undefined"
  • Fastest way to iterate over all the chars in a String
  • R - Using loops to search one variable with another…
  • MySQL database is not receiving any data in PHP
  • sql query to find priority jobs
  • Trouble with qsort with key/value structs in a btree
  • Vue + VUEX + Typescript + Vue Router. component not…
  • java.sql.SQLException: Access denied for user…
  • mysql.h file can't be found
  • Sort table rows In Bootstrap
  • Operand type clash: int is incompatible with date +…
  • How to fix overlapping issue in the Qweb reports?
  • Using Auto Layout in UITableView for dynamic cell…
  • How to create a temporary table in SSIS control flow…
  • How to UPSERT (MERGE, INSERT ... ON DUPLICATE…
  • How to see log files in MySQL?
  • How to simplify sequential logic design by…
  • MySQL fails on: mysql "ERROR 1524 (HY000): Plugin…
  • XAMPP - MySQL shutdown unexpectedly
  • fail to start mysql on CentOS7
  • Aurelia / Typescript - Error when trying to set one…
  • How to use Servlets and Ajax?
  • SQL find sum of entries by date including previous date
  • What does "dereferencing" a pointer mean?
  • Error in MySQL when setting default value for DATE…
  • How do I get the current time zone of MySQL?
  • Access denied for user 'root'@'localhost' while…
  • Apache server keeps crashing, "caught SIGTERM,…
  • Log record changes in SQL server in an audit table
  • Component Inheritance with vue js
  • How to dynamically add and remove views with Ember.js
  • How to include a PHP variable inside a MySQL statement
  • How to 'insert if not exists' in MySQL?
  • ERROR 2013 (HY000): Lost connection to MySQL server…
  • Pivoting a defined number of rows into columns…
  • Can't connect to local MySQL server through socket…
  • Using enums in a spring entity
  • com.mysql.jdbc.exceptions.jdbc4.CommunicationsExcept…
  • Smart way to truncate long strings
  • How do I obtain a Query Execution Plan in SQL Server?
  • Adding Dynamic Input Fields With VueJs
  • "INSERT IGNORE" vs "INSERT ... ON DUPLICATE KEY UPDATE"
  • How do I import a .sql file in mysql database using PHP?
  • Syntax error due to using a reserved word as a table…
  • HQL Responding with…
  • Android- Error:Execution failed for task…
  • Converting Oracle SQL Procedure into MySQL Stored Procedure
  • Multiple queries executed in java in single statement
  • How to shrink/purge ibdata1 file in MySQL
  • Best practice multi language website
  • Mac OS X - EnvironmentError: mysql_config not found
  • Best way to select random rows PostgreSQL
  • When should I use a table variable vs temporary…
  • Update MySQL version from 5.1 to 5.5 in CentOS 6.2

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

String representation of an Enum

Next Post:

typedef struct vs struct definitions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error