CODEWITHSUNDEEP
mysqlsqlsql-insert
james Oduro
james Oduro

Reputation: 673

How do I update if exist, else insert in MySQL

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}')

Upvotes: 1

Views: 72

Answers (2)

GMB
GMB

Reputation: 222402

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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);

Upvotes: 1

Related Questions