Combine two queries to check for duplicates in MySQL?

Question

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?

Answer 1

There are actually two problems in your question. The first problem is to make Number column unique and the second one is to increment the column Name by appending a number if it already exists.

FIRST PART

Since the number is UNIQUE, enforce a UNIQUE constraint on the column. It could be a PRIMARY KEY or a UNIQUE KEY.

If the column has no KEY and you want to make it PRIMARY, here is the ALTER statement:

ALTER TABLE TableName ADD CONSTRAINT tb_pk PRIMARY KEY (Number)

• SQLFiddle Demo

but if you only want it to be UNIQUE and not a primary key,

ALTER TABLE TableName ADD CONSTRAINT tb_uq UNIQUE (Number)

• SQLFiddle Demo

SECOND PART

You can actually do it without using join.

INSERT INTO TableName(Number, Name)
SELECT  124 AS Number, 
        CONCAT('Robert', COALESCE(MAX(CAST(REPLACE(Name, 'Robert', '0') AS UNSIGNED)) + 1,'')) AS Name
FROM    TableName
WHERE   Name LIKE 'Robert%'

• SQLFiddle Demo
• SQLFiddle Demo (added more example)
• SQLFiddle Demo (throws exception due to uniqueness)

Some details:

when the value supplied on column Number already exists, it will throw an error since the column is unique. I have read a comment from a deleted posts saying: "..Number is not unique, but if it does exist, I don't want to enter a record." -- it does not make any sense if you don't want to add uniqueness on the column. How will you know if the number already exists or not? Doing a little check for the existence of Number feels like a little overhead for me. So my best recommendation is to enforce uniqueness.

Answer 2

SELECT * FROM Person WHERE Number = 123 OR Name = 'Robert'

I haven't worked with SQL for some time, so this may be wrong ;)

Edit:

$number = 123;
$name = 'Robert';
$query = mysql_query("SELECT * FROM Person WHERE Number = $number OR Name = '$name' ");

if (mysql_num_rows($query) == 0 ) {
//-> Add your record, it's unused
} else if (mysql_result($query, 0, 'number') == $number && mysql_result($query, 0, 'name' == $name)) {
//combination of number and name already exists -> modify name and add record
} else {
echo "Number is used by another name";
}

Answer 3

Use this query, for insert the row [123, 'Robert']. if you want insert other values, change 123 & Robert values in below query:

insert into Person (Number,Name)
select 123, IF(mn.MaxNumber is NULL,'Robert',concat('Robert',mn.MaxNumber+1))
from (SELECT 'foo') foo
left JOIN (select max(CONVERT(SUBSTR(Name,LENGTH('Robert')+1),UNSIGNED)) `MaxNumber`
            from person where name rlike '^Robert[0-9]*$') mn on 1=1
where Not Exists (select * from Person where Number=123)

NOTE: if Robert exists in the table, above query inserts Robert1. if Robert1 exists, it inserts Robert2, and so on .

Answer 4

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.