sql - Combine two queries to check for duplicates in MySQL? -

i have table looks this:

number  | name  --------+-------- 123     | robert 

this want do:

if number in database, don't insert new record.

if number not in databse, name is, create new name , insert it. example, if have record contains 123 number , bob name, don't want insert it, if record contains 456 number , robert name, insert 456 , robert1. going check duplicates individually like:

select * person number = 123;  //if number not found select * person name = 'robert';  //if name found, add number it. 

is there way can combine 2 statements?

there 2 problems in question. first problem make number column unique , second 1 increment column name appending number if exists.

first part

since number unique, enforce unique constraint on column. primary key or unique key.

if column has no key , want make primary, here alter statement:

alter table tablename add constraint tb_pk primary key (number) 

• sqlfiddle demo

but if want unique , not primary key,

alter table tablename add constraint tb_uq unique (number) 

• sqlfiddle demo

second part

you can without using join.

insert tablename(number, name) select  124 number,          concat('robert', coalesce(max(cast(replace(name, 'robert', '0') unsigned)) + 1,'')) name    tablename   name 'robert%' 

• sqlfiddle demo
• sqlfiddle demo (added more example)
• sqlfiddle demo (throws exception due uniqueness)

some details:

when value supplied on column number exists, throw error since column unique. have read comment deleted posts saying: "..number not unique, if exist, don't want enter record." -- not make sense if don't want add uniqueness on column. how know if number exists or not? doing little check existence of number feels little overhead me. best recommendation enforce uniqueness.