asked on

Insert multiple rows and columns into MySQL while ignoring duplicates in one column

I want to insert rows into MySQL for three columns, but ignore the insert if ALL THREE COLUMNS are duplicates

Here's my current sql statement in PHP (example):

$sql = "INSERT INTO test
(id_number, user_id, username)
VALUES
('3456','10','john'),('1234','10','john');";

This works fine, however if there was already an id_number of '1234' in the database, it will now have two such entries.

I would like 1234 to be ignored, but ONLY if:
1234 is already present in a row
user id 10 is already present in same row
username john is already present in same row

Is this possible in one SQL statement?

Thanks,
-Carlos-

JB04

which dont you use an auto increment column ?

that way all id_number records will be unique

twstdpairs

I agree with JB04. That will definitely be good practice I'd say - setting the id_number as an auto_increment index. You could also incorporate some error checking that will supply the user with a message.

After looking at your post, and forgive me if I interpret incorrectly, but it looks like you mean to check duplication on the user_id field and if so, not generate a new entry with a new id_number. Is that what you are trying to do?

Muhammad Wasif

Either you can use INSERT IGNORE

$sql = "INSERT IGNORE INTO test
(id_number, user_id, username)
VALUES
('3456','10','john'),('1234','10','john')";

But to use this, you must need to set the columns to unique. For more details http://dev.mysql.com/doc/refman/4.1/en/insert.html

Muhammad Wasif

No need to use ; in the query like you did in your first query
$qry = "INSERT IGNORE into test (column1) values('value1'), ('value2');";
^
It should be like this
$qry = "INSERT IGNORE into test (column1) values('value1'), ('value2')";
^
Note the difference of ;

solrac149

ASKER

Re: Wasifg
Thanks for your comment!
In needing to remove the ; (semicolon) from the end of the query
Not true: My sql query is running inside of a loop in PHP, therefore the semicolon at the end of the query is necessary

Re: Twstdpairs and JB04
Thanks for your comments!
The id_number field is a value imported from another set of data. It is not to be auto-generated by mySQL

Re: Wasifg
Thanks for your tip!
The INSERT IGNORE seems like it might work... but the INDIVIDUAL columns are not unique --

If I have a row like this:
'3456', '10', 'John'

Then I can NOT have another row like this:
'3456', '10', 'John'

But I CAN have another row like this:
'3456', '5', 'Bob'

Will the IGNORE statement still work this way? Thanks for all your help so far!

Muhammad Wasif

If your id_number column is unqiue, then it will not insert this row.

solrac149

ASKER

It didn't work that way, I tried it...

if id_number column is unique, then the row:
'3456', '5', 'Bob'

Will not be inserted either.

It needs to be some kind of ... if ALL columns are identical

Something like this:

$sql = "INSERT IGNORE {{{{ALL COLUMNS}}}} INTO test
(id_number, user_id, username)
VALUES
('3456','10','john'),('1234','10','john')";

Then it will insert EVERYTHING EXCEPT those where all three values are identical to an already existing row. Is this possible?

Thanks so much for any help!

-Carlos-

ASKER CERTIFIED SOLUTION

Muhammad Wasif

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

solrac149

ASKER

Thank you for that info, wasifg!
This works but results in an error when duplicates are found, instead of ignoring them

But INSERT IGNORE INTO test

worked

This is the solution!! I will add another 50 points and accept. Thanks!

Muhammad Wasif

oooppps

I forgot to add IGNORE in the query, but you are wise enough;) Thanx for the points

Muhammad Wasif

solrac149

ASKER

No problem
I am not wise enough to have known all this on my own
but at least wise enough to put the last piece together haha
Thanks again
-Carlos-