CODEWITHSUNDEEP
mysql
Elland Junsay Pansensoy
Elland Junsay Pansensoy

Reputation: 59

Insert if not exist and Update if exist

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

Upvotes: 0

Views: 99

Answers (2)

Kita
Kita

Reputation: 1558

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.

Upvotes: 0

taesu
taesu

Reputation: 4580

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.

Upvotes: 1

Related Questions