MySQL: Insert record if not exists in table
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 |
+----+---------+-----------+------+
How can I do 'insert if not exists' in MySQL?
Use INSERT IGNORE INTO table.
There's also INSERT … ON DUPLICATE KEY UPDATE syntax, and you can find explanations in 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement.
Post from bogdan.org.ua according to Google's webcache:
18th October 2007
To start: as of the latest MySQL, syntax presented in the title is not
possible. But there are several very easy ways to accomplish what is
expected using existing functionality.There are 3 possible solutions: using INSERT IGNORE, REPLACE, or
INSERT … ON DUPLICATE KEY UPDATE.Imagine we have a table:
CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;Now imagine that we have an automatic pipeline importing transcripts
meta-data from Ensembl, and that due to various reasons the pipeline
might be broken at any step of execution. Thus, we need to ensure two
things:
- repeated executions of the pipeline will not destroy our
> database
- repeated executions will not die due to ‘duplicate
> primary key’ errors.Method 1: using REPLACE
It’s very simple:
REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;If the record exists, it will be overwritten; if it does not yet
exist, it will be created. However, using this method isn’t efficient
for our case: we do not need to overwrite existing records, it’s fine
just to skip them.Method 2: using INSERT IGNORE Also very simple:
INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;Here, if the ‘ensembl_transcript_id’ is already present in the
database, it will be silently skipped (ignored). (To be more precise,
here’s a quote from MySQL reference manual: “If you use the IGNORE
keyword, errors that occur while executing the INSERT statement are
treated as warnings instead. For example, without IGNORE, a row that
duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted.”.) If the
record doesn’t yet exist, it will be created.This second method has several potential weaknesses, including
non-abortion of the query in case any other problem occurs (see the
manual). Thus it should be used if previously tested without the
IGNORE keyword.Method 3: using INSERT … ON DUPLICATE KEY UPDATE:
Third option is to use
INSERT … ON DUPLICATE KEY UPDATE
syntax, and in the UPDATE part just do nothing do some meaningless
(empty) operation, like calculating 0+0 (Geoffray suggests doing the
id=id assignment for the MySQL optimization engine to ignore this
operation). Advantage of this method is that it only ignores duplicate
key events, and still aborts on other errors.As a final notice: this post was inspired by Xaprb. I’d also advise to
consult his other post on writing flexible SQL queries.
MySQL: insert where not exists
Use insert . . . select:
INSERT INTO USER (name, email)
SELECT 'John', 'john@mmm.com'
WHERE NOT EXISTS
(SELECT id FROM USER WHERE email = 'john@mmm.com');
I would write this as:
INSERT INTO USER (name, email)
SELECT name, email
FROM (SELECT 'John' as name, 'john@mmm.com' as email) t
WHERE NOT EXISTS (SELECT 1 FROM USER u WHERE u.email = t.email);
But a better approach is probably to just put in a unique index so the database protects the data:
create unique index idx_users_email on user(email);
MySQL INSERT INTO WHERE NOT EXIST
You can either use INSERT IGNORE or REPLACE or INSERT ... ON DUPLICATE KEY UPDATE ...
Each requires you to have a unique constraint on product_id, ip_address
INSERT IGNORE
INSERT IGNORE INTO products VALUES (null, 111, '8.8.8.8')
will ignore the insert, if entry already exists.
REPLACE
REPLACE INTO products VALUES (null, 111, '8.8.8.8')
will perform a delete plus a new insert.
INSERT ... UPDATE
INSERT INTO products VALUES (null, 111, '8.8.8.8')
ON DUPLICATE KEY UPDATE products SET last_modified = NOW()
Will try to insert and if that fails update the existing record.
In your case I think you should be fine with INSERT IGNORE, however If you want to change other values if the record already exists, INSERT ... UPDATE ... should work well.
I generally would not recommend to use REPLACE unless you really want a DELETE FROM table WHERE ... plus INSERT INTO TABLE ...
Update
This requires (for this example) a unique index for the combination product, ip_address.
You can achive this with
ALTER TABLE products
ADD UNIQUE INDEX `UIDX_PRODUCT__IP_ADRESS`(product, ipaddress);
IF NOT EXISTS then INSERT
You can use IGNORE keyword here.
It could look like:
INSERT IGNORE INTO series (seriesName) VALUES 'Avengers'
The important thing is to create a unique key on seriesName field as it seems that you want it to be unique.INSERT IGNORE doesn't make the insert when key value already exists.
If you would like to be able to get id (primary key value) for row that wasn't inserted (already existed), you can do the following trick:
INSERT IGNORE INTO series (seriesName) VALUES 'Avengers'
ON DUPLICATE KEY UPDATE seriesID= LAST_INSERT_ID(seriesID)
Then you will be able to get the ID with LAST_INSERT_ID() function no matter if the row was inserted or not.
mysql - insert if not exist
In the list after the table name you must list the target columns, not the values. The values go into the list after SELECT. And a FROM-less SELECT doesn't seem to work in MySQL when there's a WHERE clause. You can use FROM dual. So syntactically you would need to write:
INSERT INTO user_last_view
(userid,
page,
refid,
mode)
SELECT 1,
'res',
2, 'edit'
FROM dual
WHERE NOT EXISTS (SELECT *
FROM user_last_view
WHERE userid = 1
AND page = 'res'
AND refid = 2
ORDER BY createdate
DESC LIMIT 10);
But that won't do what you want because it gets the last ten matching rows, not the last ten in general and then checks if one matches. Instead of directly selecting from the table, select from a derived table that gets the last ten rows regardless of a match and then checks for a match within those.
INSERT INTO user_last_view
(userid,
page,
refid,
mode)
SELECT 1,
'res',
2, 'edit'
FROM dual
WHERE NOT EXISTS (SELECT *
FROM (SELECT ulv.userid,
ulv.page,
ulv.refid
FROM user_last_view ulv
ORDER BY ulv.createdate
DESC LIMIT 10) x
WHERE x.userid = 1
AND x.page = 'res'
AND x.refid = 2);
Related Topics
Error When Trying to Install App With MySQL2 Gem
How Does Database Indexing Work
Two SQL Left Joins Produce Incorrect Result
How to Comma Delimit Multiple Rows into One Column
Using an Alias in a Where Clause
Concatenate Columns in Apache Spark Dataframe
Update Multiple Rows With Different Values in One Query in MySQL
Is There an Oracle SQL Query That Aggregates Multiple Rows into One Row
Correct MySQL Configuration For Ruby on Rails Database.Yml File
Select Rows Which Are Not Present in Other Table
How to Delete Duplicate Rows in SQL Server
Is There a Combination of "Like" and "In" in Sql
Count the Number of Occurrences of a String in a Varchar Field