Insert if not exist and Update if exist

Question

I have three columns on my database table

user_id, post_id, and vote

I want to insert a new data if user_id and post_id don't exist. But if both columns user_id and post_id exist i will be able to update 'vote' column value. I set user_id to be unique but it proves to be not working since i want user to insert votes on different post.

The query below only updated the value of vote since user_id already exist. I want to have it updated if and only if user_id and post_id existed

I used this sql query

 INSERT INTO polls (user_id,post_id,vote) VALUES (1,2,5)
  ON DUPLICATE KEY UPDATE vote= ?;

Here's my problem

taesu · Accepted Answer · 2015-02-01 06:54:30Z

1

You must create unique key combination

Create unique index your_index_name on yourtable (field_one,field_two),
then do the insert into , on duplicate key logic

It is absolutely logical that your code does not work as intended, because your only key is user_id, thus if you want to check the uniqueness of user_id AND post_id, then you should set it as so.

answered Feb 1, 2015 at 6:54

taesu

4,5905 gold badges26 silver badges41 bronze badges

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

4 Comments

taesu Over a year ago

Play with the columns, what is the type of post_id? have you removed the uniqueness of user_id first?

Elland Junsay Pansensoy Over a year ago

same problem if i remove uniqueness of user_id. I want user to vote on different posts, so if user already voted to that post it will just update the his vote not insert another row

taesu Over a year ago

given answer is the solution. I don't know how you are setting the keys, but try again. remove uniquness of user_id , instead make user_id and post_id combo key.

Elland Junsay Pansensoy Over a year ago

@taesu so what would be my query look like? if i remove both user_id and post_id uniqueness??

Kita · Accepted Answer · 2015-02-01 06:58:09Z

0

Don't think you can do it purely in MySQL :*( post_id would have to be unique and you said that does not fit your business logic. Furthermore, if multiple keys are detected, they are joined by an OR in the resulting query, which will further complicate things for you. Here's an excerpt from the manual, where a and b are keys involved in the ON DUPLICATE KEY query:

If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

answered Feb 1, 2015 at 6:58

Kita

1,56814 silver badges21 bronze badges

2 Comments

Elland Junsay Pansensoy Over a year ago

Kita any idea how will i achieve that?

Kita Over a year ago

You'd have to do it on the server side. First, issue a SELECT for that pair of user_id and post_id. If you find a matching record, update it. Otherwise do an insert. It's basically moving the "does this combo already" exist check out of SQL and into your server side language of choice. Does that make sense?

Collectives™ on Stack Overflow

Insert if not exist and Update if exist

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related