Insert query check if record exists - If not, Insert it
You can use below query. Here it will insert the ip_address when it is not present in your table.
INSERT INTO ip_list (ip_addr)
SELECT * FROM (SELECT '192.168.100.1') AS tmp
WHERE NOT EXISTS (
SELECT ip_addr FROM ip_list WHERE ip_addr='192.168.100.1'
);
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 |
+----+---------+-----------+------+
Check if record exists then insert new row in database table?
I don't know/work-with coldfusion so not sure I'm reading the logic correctly ...
- if record does not exist in
table1
but - record does exit in
contact
then - insert a row into
inter_work_tbl
The general T-SQL query would look like (note: mixing T-SQL with references to the coldfusion variables):
insert into inter_work_tbl
(user_id
,first_name
,last_name
,password)
select '#session.user_id#',
c.fname,
c.lname,
'#password#'
from contact c
where c.userid = #session.user_id#
and not exists(select 1
from table1 t
where t.user_id = c.userid)
SQL Server Insert if not exists
instead of below Code
BEGIN
INSERT INTO EmailsRecebidos (De, Assunto, Data)
VALUES (@_DE, @_ASSUNTO, @_DATA)
WHERE NOT EXISTS ( SELECT * FROM EmailsRecebidos
WHERE De = @_DE
AND Assunto = @_ASSUNTO
AND Data = @_DATA);
END
replace with
BEGIN
IF NOT EXISTS (SELECT * FROM EmailsRecebidos
WHERE De = @_DE
AND Assunto = @_ASSUNTO
AND Data = @_DATA)
BEGIN
INSERT INTO EmailsRecebidos (De, Assunto, Data)
VALUES (@_DE, @_ASSUNTO, @_DATA)
END
END
Updated : (thanks to @Marc Durdin for pointing)
Note that under high load, this will still sometimes fail, because a second connection can pass the IF NOT EXISTS test before the first connection executes the INSERT, i.e. a race condition. See stackoverflow.com/a/3791506/1836776 for a good answer on why even wrapping in a transaction doesn't solve this.
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.
SQL check if non-unique record exists—If it does, UPDATE; if not, INSERT the record INTO the table
Because I could not add a unique index/constraint on (post_id, meta_key)
(because my database happened to already have duplicate post_id/meta_key rows), here's what ended up working for me.
Hopefully this helps someone in the future, let me know if there's any problems with the solution:
REPLACE INTO wp_postmeta (meta_id, post_id, meta_key, meta_value)
SELECT
IFNULL(
(SELECT n.meta_id FROM wp_postmeta n
WHERE n.meta_key = '_new_column_name'
AND n.post_id = o.post_id ),
NULL
),
o.post_id, '_new_column_name', o.meta_value
FROM wp_postmeta o
WHERE meta_key = '_old_column_name';
If record exists dont insert
You need to change your query a bit, but you can use MySql's DUAL keyword to do this:
string _connStr = @"Data Source = EJQ7FRN; Initial Catalog = BES; Integrated Security = True";
string _query = "INSERT INTO [BES_S] (ISN,Titel,Name) ";
_query = _query + " SELECT @ISN, @Titel, @Name FROM DUAL";
_query = _query + " WHERE NOT EXISTS (SELECT ISN WHERE ISN=@ISN)";
using (SqlConnection conn = new SqlConnection(_connStr))
{
using (SqlCommand comm = new SqlCommand())
{
comm.Connection = conn;
comm.CommandType = CommandType.Text;
comm.CommandText = _query;
comm.Parameters.AddWithValue("@ISN", txtISN.Text);
comm.Parameters.AddWithValue("@Titel",txtTitel.Text);
comm.Parameters.AddWithValue("@Name", txtName.Text);
try
{
conn.Open();
comm.ExecuteNonQuery();
}
catch (SqlException ex)
{
}
}
}
DUAL
is like a dummy table that you can use to SELECT
from.
Related Topics
PHP Session for Tracking Unique Page Views
PHP Foreach by Reference Causes Weird Glitch When Going Through Array of Objects
PHP How to Retrieve Array Values
Issues with PHP 5.3 and Sessions Folder
Codeigniter Check for User Session in Every Controller
Increase Days to PHP Current Date()
Easiest Way to Alternate Row Colors in PHP/Html
Refresh a Table with Jquery/Ajax Every 5 Seconds
Is PHP's 'Include' a Function or a Statement
Bad Request. Connecting to Sites via Curl on Host and System
What Is Normalized Utf-8 All About
Why Are PHP Function Calls *So* Expensive
"Adaptive Server Is Unavailable or Does Not Exist" Error Connecting to SQL Server from PHP