How do I update if exist, else insert in MySQL

Question

I have table with AUTO_INCREMENT field defined as PRIMARY_KEY.

I have columns like: vote_id,vote_user_id,vote_ask_id,vote_comment_id,vote_post_id,vote_type,vote_status

I want to INSERT new records but before I do that I want to check if there is a row with columns(vote_user_id,vote_ask_id,vote_type) as same as the new data I want INSERT.

CONDITIONS:
IF ROW EXISTS
THEN UPDATE tableName SET vote_status=new_value, vote_time=new_time
ELSE
INSERT NEW RECORDS

I have searched the internet and learnt about MySQL ..ON DUPLICATE KEY UPDATE. I have realize this statement will not be helpful to my task since it only checks for DUPLICATE KEY(...PRIMARY_KEY or UNIQUE FIELD). I have learnt also on MySQL REPLACE INTO ...and likewise this will not be helpful to my problem since that is also bind to PRIMARY_KEY or UNIQUE index.

I learnt I could use MERGE....USING...statements but this was giving me errors so i read more about it and I relised it only work in SQL server (Microsoft)

Please how best can someone help me solve this?

I tried this on MERGE staments:

MERGE {$votes_table} WITH (HOLDLOCK) AS VT
    USING ({$Qid},{$vote_type},{$CUid},{$vote_status}) AS VTS (vote_ask_id,vote_type,vote_user_id,vote_status)
    ON( VT.vote_ask_id = VTS.vote_ask_id AND VT.vote_user_id=VTS.vote_user_id AND VT.vote_type=VTS.vote_type)
    WHEN MATCHED THEN 
    UPDATE SET VT.status=VST.vote_status , VT.vote_time='{$current_time}' WHERE VT.vote_user_id=VTS.vote_user_id AND VT.vote_ask_id=VTS.vote_ask_id AND VT.vote_type=VTS.vote_type

    WHEN NOT MATCHED THEN INSERT (vote_ask_id,vote_type,vote_status,vote_user_id,vote_time) VALUES('{$Qid}','{$vote_type}','{$vote_up_status}','{$CUid}','{$current_time}')

Is there any case where you have more than one row that contains the tuple (vote_user_id,vote_ask_id,vote_type) - if yes, how should that merge algorithm handle this? If not, what keeps you from using a unique index and go further with ON DUPLICATE KEY? — Nico Haase
– Nico Haase, Commented Mar 6, 2020 at 15:31

Gordon Linoff · Accepted Answer · 2020-03-06 15:31:32Z

1

In MySQL, use ON DUPLICATE KEY:

INSERT INTO tablename (vote_user_id, vote_ask_id, vote_type, . . . )
    VALUES (new_vote_user_id, new_vote_ask_id, new_vote_type . . . )
    ON DUPLICATE KEY UPDATE vote_status = VALUES(vote_status), vote_time = VALUES(vote_time);

For this to work, you need a unique index/constraint on the columns that define a unique row:

CREATE UNIQUE INDEX unq_tablename_3 ON tablename(vote_user_id, vote_ask_id, vote_type);

answered Mar 6, 2020 at 15:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

james Oduro Over a year ago

thanks very much. I never knew i could create a unique index/constraint on multiple columns. Your solution worked. :)

GMB · Accepted Answer · 2020-03-06 16:05:48Z

0

You do want insert ... on duplicate key update: in MySQL, this is the right way to do what you want.

To start with, you need to create a unique constraint on the tuple of concerned columns:

create unique index votes_table_unique_idx 
    on votes_table(vote_user_id, vote_ask_id, vote_type);

Then, you can do something like:

insert into votes_table(vote_user_id, vote_ask_id, vote_type, vote_comment_id, vote_post_id, vote_status)
values(...)
on duplicate key update
    vote_comment_id = values(vote_comment_id),
    vote_post_id    = values(vote_post_id)
    vote_status     = values(vote_status)

edited Mar 6, 2020 at 16:05

answered Mar 6, 2020 at 15:32

GMB

224k25 gold badges103 silver badges151 bronze badges

5 Comments

james Oduro Over a year ago

I got syntax error in your code on line....ON DUPLICATE KEY UPDATE SET... thanks for your efforts to help.

GMB Over a year ago

@jamesOduro: you just have to remove the set keyword, which was a typo. I updated my answer accordingly.

james Oduro Over a year ago

yes. I noticed that thats why i couldn't accept your answer. but thanks very much also

james Oduro Over a year ago

and again problem with VALUES(...) in the ON DUPLUCATE KEY ....line

GMB Over a year ago

@jamesOduro: in the query, values(...) should be replaced with the list of values that you want to insert.

Collectives™ on Stack Overflow

How do I update if exist, else insert in MySQL

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related