Geek Answers Handbook

INSERT to table if no other result exists

I have a SQL user table structure like this (the identifier is randomly generated and not automatically 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 several deleted (deleted = 1) users with the same name, but only one changed user with the same name (therefore, adding Simon is fine, but Dave is not). How can I insert only if in one SQL query there is no record with the same name and deleted = 0? 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 the correct syntax.

+4
source share
4 answers

Install LEFT JOIN with

  • Subquery A containing your random number with an alias ID and $ name, an alias as a name.
  • Subquery B selects name = $ name, which is not deleted.
  • LEFT JOIN AB and return A. * if it does not have the right side for LEFT JOIN

This is a request

 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;
+3
source

Use the MySQL REPLACE Syntax .

Verify that the identifier is either a primary or unique key and creates an index for all three identifiers, names, and deleted columns.

 REPLACE INTO users (ID, name) VALUES ($id, $name)
+1
source 
 INSERT INTO users (ID, name) SELECT $id, $name WHERE NOT EXISTS ( SELECT 1 FROM users WHERE name = $name AND deleted = 0 )
0
source 
 INSERT INTO users SELECT '1', 'Bob','','' FROM DUAL WHERE NOT EXISTS(SELECT ID FROM users WHERE name = 'Bob' AND deleted ='0' LIMIT 1)
0
source

More articles:


All Articles