CODEWITHSUNDEEP
phpmysqlinsert
Timm
Timm

Reputation: 12753

INSERT into table if a different result does not exist

I have a 'users' SQL table structure like this (the ID is randomly generated, not auto-incremented):

ID    name     deleted    lastActive
3242  Joe      0          20-6-2012 23:14
2234  Dave     0          20-6-2012 23:13
2342  Simon    1          20-6-2012 23:02
9432  Joe      1          20-6-2012 22:58

There can be multiple deleted (deleted=1) users with the same name, but only 1 undeleted user with the same name (so adding Simon is fine, but Dave is not). How can I insert only if there is not a record already with the same name and deleted=0, in one SQL query? I need something like this:

INSERT INTO users (ID, name) VALUES ($id, $name) 
    WHERE NOT EXISTS (SELECT 1 FROM users WHERE name = $name AND deleted = 0)

But this is not correct syntax.

Upvotes: 4

Views: 480

Answers (4)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44373

Setup a LEFT JOIN with

  • Subquery A containing your random number aliased as ID and $name aliased as name.
  • Subquery B selects name = $name that is not deleted.
  • LEFT JOIN A to B and return A.* if it does not have a right side counterpart for the LEFT JOIN

This is the query

INSERT INTO users (ID,name)
SELECT A.* FROM
(SELECT RAND() ID,'$name' name) A LEFT JOIN
(SELECT name FROM users WHERE name='$name' AND deleted=0) B
USING (name) WHERE B.name IS NULL;

Upvotes: 3

Bob
Bob

Reputation: 128

INSERT INTO users SELECT '1', 'Bob','','' FROM DUAL WHERE NOT EXISTS(SELECT ID FROM users WHERE name = 'Bob' AND deleted ='0' LIMIT 1)

Upvotes: 0

edwardmp
edwardmp

Reputation: 6611

Use MySQL's REPLACE syntax.

Make sure that ID Is either a primary or unique key and create a index on all three ID, name and deleted columns.

REPLACE INTO users (ID, name) VALUES ($id, $name)

Upvotes: 1

Mathieu Dumoulin
Mathieu Dumoulin

Reputation: 12244

INSERT INTO users (ID, name) 
SELECT $id, $name WHERE NOT EXISTS (
    SELECT 1 FROM users WHERE name = $name AND deleted = 0
)

Upvotes: 0

Related Questions