CODEWITHSUNDEEP
mysql
Co Koder
Co Koder

Reputation: 2221

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

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

Upvotes: 2

Views: 211

Answers (1)

rsanchez
rsanchez

Reputation: 14657

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

Upvotes: 1

Related Questions