mysql: update if exists else insert and in both case return the id

Question

I know this has been asked a lot of times and I have checked most of the questions but havent found an answer that suits my problem.

I have a table:

CREATE TABLE `users` (                                   
      `Id` int(11) NOT NULL AUTO_INCREMENT,                  
      `Name` varchar(50) NOT NULL,                           
      `Email` varchar(50) NOT NULL,                                            
      `Added_Date` date DEFAULT NULL,                                             
      PRIMARY KEY (`Id`,`Email`)                             
    )

I will be inserting records in this table from the screen wherein I will ask the users for the Name and EmailId of their friends.

What I want to do is simply check the Email id, if the same email id exists in my Users table, then update the name entered. Else insert a new record. And in both the cases I want the userId of the record that has been inserted or updated.

Sounds simple!

But I tried Replace, Insert.. on duplicate key update, insert where not exists but none of them have worked for me. This is what I have tried so far:

REPLACE

REPLACE INTO Users SET email = '[email protected]';

But this will keep on inserting new record everytime as the name is not checked. Not sure if I am using it correctly.

INSERT.. ON DUPLICATE KEY UPDATE

insert into Users (id, Name, Email, Added_date) values (null, 'ABCD', '[email protected]',current_date) on duplicate key update Name='XYZ';

This wont work since the primary key of the table is a composition of (id and emailid) and since the ID is auto-incremental, it will insert a new record everytime.

I know I can write an update query to check if there is any record with a particular email_id, if yes then it will get updated else I will insert a new record. And I can get the new incremented ID using $userId = mysql_insert_id();. But how can I get the ID in case of the update query?

I believe there has to be a better way of doing this thing than writing update and insert queries separately.

Hắc Huyền Minh · Accepted Answer · 2012-09-23 12:07:27Z

1

The primary key should be set on Users.Id only, for the Email just make it unique

CREATE TABLE `users` (                                   
  `Id` int(11) NOT NULL AUTO_INCREMENT,                  
  `Name` varchar(50) NOT NULL,                           
  `Email` varchar(50) NOT NULL,                                            
  `Added_Date` date DEFAULT NULL,                                             
  PRIMARY KEY (Id),
  UNIQUE KEY Email (Email)                            
)

And your query will be like that, note that you can you VALUES() function to retrieve the new value.

Insert into Users (Id, Name, Email, Added_date) 
values (null, 'ABCDe','[email protected]',current_date) 
on duplicate key update Name=VALUES(Name);

answered Sep 23, 2012 at 12:07

Hắc Huyền Minh

1,03511 silver badges13 bronze badges

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

1 Comment

DarkKnightFan Over a year ago

thanks.. that worked. It returns the id of the updated record as well and hence solved my other problem too!

Dima Railguner · Accepted Answer · 2012-09-23 11:31:38Z

0

I did not read your question to the end, but now I can say that the table schema is not correct. Primary key must be defined as PRIMARY KEY (Id), email must be defined as Email varchar(50) UNIQUE

answered Sep 23, 2012 at 11:31

Dima Railguner

1735 silver badges15 bronze badges

1 Comment

Akash KC Over a year ago

You can put your answer in comment section of Question as it does not contain real answer...

Collectives™ on Stack Overflow

mysql: update if exists else insert and in both case return the id

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related