1

I am a newbie to mysql. Please help. I have a table people like this. The only primary key of people is id

id  name  age sex

 1. John  16  M

 2. Peter 18  K

I would like to write some sql to insert some rows to people. But if the name is already exist in the table. I do not insert new row. For example, if I insert the row with the name John and Peter. I do not insert rows. I have a variable name as var_name;

I have search out for the web for a very long time. I use the following sql recommend by the web

INSERT into People(name) values(var_name) where not exists(SELECT name from People
where name = var_name)

But the sql syntax error comes out. Why would this happen. And is there any fast way to acheieve my goal.

Sylvain Leroux
  • 44,729
  • 6
  • 86
  • 107
user1386463
  • 163
  • 1
  • 2
  • 16
  • duplicate of http://stackoverflow.com/questions/3164505/mysql-insert-record-if-not-exists-in-table – user3741598 Jul 08 '14 at 15:21
  • Not related to your question but I have two observations. One is that in real life, more one person can have the same name. The other is that storing someone's age is of limited value because it changes over time. – Dan Bracuk Jul 08 '14 at 15:49

2 Answers2

6

The best way to do this is to create a unique index on name:

create unique idx_people_name on people(name)

Then, when you insert, use on duplicate key update:

INSERT into People(name)
    values(var_name)
    on duplicate key update name = values(name);

The update piece does nothing -- it is a "no-op". But this puts the logic in the database and enforces that names need to be unique.

For your query to work, you need insert . . . select. The values clause doesn't take a where statement:

INSERT into People(name) 
    select var_name
    from dual
    where not exists(SELECT name from People where name = var_name);
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
  • For the second method. I would like to ask do I need to change the field `name` to key? – user1386463 Jul 08 '14 at 15:26
  • @user1386463 . . . There is no need to have special names for columns. And, you don't want to name a column `key` because that is a reserved word in MySQL. – Gordon Linoff Jul 08 '14 at 15:28
  • Thanks a lot. I understand the first 2 methods. For the third method. I have use the following query INSERT into People(name) select ('Peter') where not exists (select name from People where name = 'Peter') But sql error occur. – user1386463 Jul 08 '14 at 15:45
  • The question is tagged mysql. Does it have a "dual"? – Dan Bracuk Jul 08 '14 at 15:50
  • No. Do you mean the name part must be select from a table? Because I want to assign the name value by my own, not query value from any of the table. – user1386463 Jul 08 '14 at 15:54
  • @DanBracuk . . . MySQL has a `dual`. Either MySQL or Postgres requires it when there is a `where` clause. I forget which, so I just include it for both. – Gordon Linoff Jul 08 '14 at 16:40
  • @user1386463 . . . `dual` isn't a real table. It is the same as selecting a constant. So `values(var_name)` and `select var_name` and `select var_name from dual` are all equivalent in this context. – Gordon Linoff Jul 08 '14 at 16:41
0

If you have a unique constraint on the name, I believe you can use:

INSERT IGNORE People(name) VALUES (var_name);
Igor
  • 30,885
  • 14
  • 70
  • 107