1

I read this question, which highlights a solution to conditionally insert values into a table if they don't already exist. My question: is it possible to conditionally insert multiple values at once.

For instance, say I have a table that just contains user names (this is a pointless table, but let's keep it simple). The table's contents look like this:

matthew  20
mark     24
luke     25
john     56
buddy    68

A user enters jimmy 34, mark 25 and bobby 54 in a web form and submits, and I'd like to check whether those three values exist in the table already and insert the ones that don't in one statement. Yes, for this example, I'm assuming names are unique.

Here is a paraphrase of the code snippet from the question I linked to, adapted to this example:

INSERT INTO users(name) 
SELECT 'jimmy'
    FROM dual
    WHERE NOT EXISTS (SELECT * FROM users
                         WHERE user = 'jimmy')

How can I adapt this for multiple values being inserted at once? It's also important that the solution work independently of the number of values entered. In my example, I give three (jimmy, mark and bobby) but there may only be one or there may be 20.

Second question: is this wise? I know that reducing the number of queries is desirable but is it worth it here? Should I just set up a for loop and loop through, alternately checking if a value exists and inserting if it doesn't?

Thanks for any help.

Sorry I don't have code that I've tried myself to show, I'm not even sure what to try here.

Update: added an extra column to the table. I wanted to keep things simple but need two columns to illustrate the fact that deleting a row and then inserting or updating are not what I want as they would favor the user's input over what is already in the table.

Community
  • 1
  • 1
itsmequinn
  • 1,054
  • 1
  • 8
  • 21

3 Answers3

0

I am not big on programming(especially on php), but can you parse the string into separate names and then do the loop?

something like (hopefuly the syntax is somewhat correct)

$names = split(" ", $user_names);
for ($i=0; $i<count($names); i++)
 {
  //sql query check - add new name function
  function_sql_query($names[$i]);
 }

In addition, you can add rules to your SQLDBMS to disallow dublicates in this attribute and add only unique records; this process will be maintained by DBMS

Andrew
  • 7,619
  • 13
  • 63
  • 117
0

See INSERT IGNORE combined with a UNIQUE KEY on the field in question.

When a unique key constraint fails the whole row is silently ignored.

ALTER TABLE users ADD UNIQUE KEY `name` (`name`);
INSERT IGNORE INTO `users` (`name`, `age`) 
  VALUES ("jimmy", 22), ("bob", 45), 
         ("luke", 300), ("john", 456);

Note: this will also suppress errors when datatypes mismatch and accurate conversion is impossible. (e.g. DECIMAL vs INT) MySQL will continue using the nearest result possible. (e.g. INT) You should ensure only pre-validated data is inserted with such statement.

Kaii
  • 20,122
  • 3
  • 38
  • 60
  • I've read that INSERT IGNORE suppresses errors, but in this case is the only error that would be generated an error showing that the insert for a given value failed because it would result in a duplicate? – itsmequinn Feb 24 '12 at 22:06
  • @itsmequinn updated answer. the feature is exactly for that purpose – Kaii Feb 24 '12 at 22:08
  • Okay. it would also suppress data conversion errors and use the nearest conversion instead. cuz i know what i'm inserting (pre-validated), that always worked great for me. – Kaii Feb 24 '12 at 22:12
-1

You can use MySQL's REPLACE

http://dev.mysql.com/doc/refman/5.0/en/replace.html

OR INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c;
Ben English
  • 3,900
  • 2
  • 22
  • 32
  • From what I'm reading there, it seems that that would delete the rows that have the same id as the rows I'm trying to insert, then insert the rows. What I want is for the insert not to happen at all if the row's id matches the value I'm trying to insert. – itsmequinn Feb 24 '12 at 21:44
  • 1
    I believe ON DUPLICATE KEY method is preferable for performance –  Feb 24 '12 at 21:45
  • @itsmequinn delete/reinsert is essentially the same thing as you are asking at least from a result standpoint. The ON DUPLICATE KEY method is preferable, in the case of a duplicate record the row won't be deleted it will just be updated (in my example I set the value to be itself - essentially leaving it unchanged – Ben English Feb 24 '12 at 21:47
  • 1
    @Ben it's not the same if there are other columns that contain data that you will be deleting. ie, you've added that jimmy's age is 20 previously. You'd lose that info. – jb. Feb 24 '12 at 21:49
  • Yes, I'm going to update the question to add another column for that reason. I wanted to keep things simple, but in reality the table I'd like to create will have other fields. – itsmequinn Feb 24 '12 at 21:56
  • @jb Then use the INSERT INTO TABLE ... ON DUPLICATE method – Ben English Feb 24 '12 at 22:16