CODEWITHSUNDEEP
mysqlsql
user2881809
user2881809

Reputation:

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

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?

Upvotes: 1

Views: 227

Answers (3)

cha
cha

Reputation: 10411

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.

Upvotes: 4

Ja͢ck
Ja͢ck

Reputation: 173662

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

Upvotes: 2

Jason Heo
Jason Heo

Reputation: 10246

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)

Upvotes: 0

Related Questions