Alex Rivera | Logout

Combine two queries to check for duplicates in MySQL?

Asked 2013-05-08T20:13:20.370
8

I have a table that looks like this:

Number  | Name 
--------+--------
123     | Robert

This is what I want to do:

If the Number is already in the database, don't insert a new record.

If the Number is not in the databse, but the name is, create a new name and insert it. So for example, if I have a record that contains 123 for Number and Bob for Name, I don't want to insert it, but if I get a record that contains 456 for Number and Robert for name, I would insert 456 and Robert1. I was going to check for duplicates individually like:

SELECT * FROM Person where Number = 123;

//If number is not found
SELECT * FROM Person where Name = 'Robert';

//If name is found, add a number to it.

Is there a way I can combine the two statements?

Edit
Report

1 Answer

1

make both number and name unique.

   ALTER TABLE  `person` ADD UNIQUE (`number` ,`name`); 

You can now do a insert with ON DUPLICATE

INSERT INTO `person` (`number`, `name`, `id`) VALUES ('322', 'robert', 'NULL')       ON DUPLICATE  KEY UPDATE `id`='NULL';

For appending a number after name i would suggest using autoincrement column instead.

answered 2013-05-17T16:08:41.707

Your Answer