insert if not exist, update if exist and a condition is true

Question

I have a table that contains id(primary key), user_id and name. I want to insert a new record if the user_id is different than the existing user_ids, and also if the user_id is the same but the name of this user_id is changed, the name should be updated. How to do that. I have done this, but it does not update for the same user_id with a different name.

CREATE TABLE test ( id int auto_increment primary key, user_id int, name varchar(60) ); INSERT INTO test (user_id,name) VALUES('01','user1'); INSERT INTO test (user_id,name) VALUES('02','user2'); INSERT INTO test (user_id,name) VALUES('03','user3'); insert into test (user_id, name) values('01', 'new_user') on duplicate key update name=values(name);

Here is the sqlfiddle: http://sqlfiddle.com/#!2/aa18e/1

rsanchez · Accepted Answer · 2013-09-19 23:34:11Z

1

You need to declare user_id as UNIQUE, like:

CREATE TABLE test ( id int auto_increment, user_id int, name varchar(60), primary key(id), unique key(user_id) );

Updated fiddle: http://sqlfiddle.com/#!2/6a5d1/1

edited Sep 19, 2013 at 23:34

answered Sep 19, 2013 at 23:25

rsanchez

14.7k1 gold badge40 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Co Koder Over a year ago

Is there a way without making user_id as unique, or if i make two columns unique together instead only user_id, your suggestion will work?

rsanchez Over a year ago

@CoKoder you said you wanted to insert a record if the user_id does not exist, and update if the user_id exists. The only way to do that with an insert ... on duplicate key statement is declaring user_id as unique.

rsanchez Over a year ago

@Mihai what do you mean?

Collectives™ on Stack Overflow

insert if not exist, update if exist and a condition is true

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related