How make insert if select rows == '0' in one query?

Question

In mysql, I have the following:

Structure Table:

id(int primary key)
name(varchar 100 unique)

Values:

id  name

1   test
2   test1

I have two queries:

1) SELECT count(*) FROM Table WHERE name='test'

2) if count select rows == 0 second query INSERT INTO Table (name) VALUES ('test')

I know that may be use:

$res = mysql(SELECT count(*) as count FROM Table WHERE name='test');
// where mysql function make query in db

$i = $res -> fetch_assoc();

if($i['count'] < 1 ){$res = mysql(INSERT INTO Table (name) VALUES ('test');}

But I would like know how to make two query in one query.

How do I make one query inside of two?

possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — alko, Nov 25 '13 at 06:10

cha · Answer 1 · 2013-11-25T04:37:21.883

You can do it with a simple trick, like this:

insert into Table1(name)
select 'test' from dual
where not exists(select 1 from Table1 where name='test');

This will even work if you do not have a primary key on this column.

Explanation: DUAL is a special dummy table that is only referenced here to enable the WHERE clause. You would not be able to have a statement without a FROM clause (like select 'test' where not exists(select 1 from Table1 where name='test')) as it will be incomplete.

Ja͢ck · Accepted Answer · 2013-11-25T04:34:02.923

Assuming your name column has a UNIQUE constraint, just add IGNORE to the INSERT statement.

INSERT IGNORE INTO Table (name) VALUES ('test')

This will skip the insertion if a record already exists for a particular value and return 0 affected rows. Note that a primary key is also considered a UNIQUE constraint.

If the name column doesn't have such a constraint, I would advice that you add one:

ALTER TABLE `Table` ADD UNIQUE(name)

See also the documentation for INSERT

score 0 · Answer 3 · answered Nov 25 '13 at 04:19

0

If you don't need to check whether there is duplication, other's suggestion is good for you. But you need, use 'INSERT' and check error number like this:

mysql_query('INSERT INTO ...');

if (mysql_errno() == 1062)
{
    echo "duplicated";
}
else
{
    echo "inserted";
}

(I know mysql_XXXX() is deprecated.. just example)

answered Nov 25 '13 at 04:19

Jason Heo

9,036
2
30
50

...provided you pass in a primary key, which normally you wouldn't on an insert like this. Also, don't use **mysql** functions, they are deprecated. – scrowler Nov 25 '13 at 04:20
@scrowler all of `INSERT IGNORE`, `REPLACE`, `INSERT .. ON DUPLICATE ERROR` need that table has PK or UNIQUE. So I assuming so.. – Jason Heo Nov 25 '13 at 04:21
Your example doesn't though – scrowler Nov 25 '13 at 04:23
@scrowler sorry, my English is bad. Could you elaborate on that? even if table has PK or UNIQUE? My code could not check duplication error? – Jason Heo Nov 25 '13 at 04:26
Take `INSERT INTO table(nonprimaryfield1, nonprimaryfield1, nonprimaryfield1) VALUES('john', 'test', 'hello')` - this will **never** throw a duplicate error, because the primary key will always increment. As this is the most common format for standard PHP SQL queries, you answer is not likely to be applicable in most cases. – scrowler Nov 25 '13 at 04:28
@scrowler he didn't give us any information, there is AUTO_INCREMENT. If `name` is UNIQUE key, my query throws 1062 error. – Jason Heo Nov 25 '13 at 04:31

How make insert if select rows == '0' in one query?

3 Answers3