Mysql if exist update else insert

Question

I Sql server i use this command to insert value and if the id already exist update the vale

string commandLine = "IF NOT EXISTS(SELECT clientid FROM Rating WHERE clientid = " + clientId + " AND userid = " + userid + ") " +
            "INSERT INTO Rating VALUES(@clientId,@userid,@rating) " +
            "ELSE " +
            "UPDATE Rating SET rating=@rating WHERE clientid = " + clientId + " AND userid = " + userid + ";";

And i now move to MySQL and this command won't work. there is any thing same in MySQL?

What doesn't work? Does it give you an error? Also, your code looks a bit confused. You appear to be switching between concatenating values into your sql(bad!!!), and using parameters (good!!!). It might make things a bit easier if you straighten that out. — cadrell0
– cadrell0, Commented Feb 26, 2013 at 22:42

marekful · Accepted Answer · 2021-08-24 20:56:48Z

7

The INSERT ... ON DUPLICATE KEY UPDATE provides an easier syntax in MySQL and also gives you feedback as to what was happening via the affected-rows API call. Few people realise in my experience how handy it can come in your program logic to know that:

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

edited Aug 24, 2021 at 20:56

answered Feb 26, 2013 at 22:44

marekful

15.4k6 gold badges39 silver badges63 bronze badges

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

2 Comments

YosiFZ Over a year ago

And if i don't have promary key?

marekful Over a year ago

Then add one. You should have one. Try learning about indexing in MySQL because it makes a big difference if you use them properly. This is a good place to start

Hamlet Hakobyan · Accepted Answer · 2013-02-26 22:46:37Z

2

If you have unique constraint or primary key on clientid, userid you can use INSERT ... ON DUPLICATE KEY UPDATE Syntax

INSERT INTO Rating VALUES(@clientId,@userid,@rating)
ON DUPLICATE KEY
UPDATE Rating SET rating=@rating

answered Feb 26, 2013 at 22:46

Hamlet Hakobyan

33.5k6 gold badges55 silver badges70 bronze badges

Collectives™ on Stack Overflow

Mysql if exist update else insert

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related