Update a row if exists otherwise insert

Question

I have a table 'match' like

id|user1|user2|paired
--+-----+-----+--------+
1 |U_1  |null |false

I need to match a new user 'U_2' to a record where paired = false, or create a new entry in table if no unpaired row is found.

This db is connected to a server where multiple users might be trying to get paired, so I need to find best possible solution that makes it fast so it doesn't lock the table for long.

the solution I came up with was

int matchId = select id from match where ((user1 != 'U_2') AND (paired = false));

if(matchId > 0)
then
   update table match set user2 = 'U_2' where id = matchId; 
else
   insert new row.

Please suggest a better way.

Thanks in advance.

Your existing solution looks fine to me. This looks very simple, are you seeing a specific performance issue here? — worpet, Dec 01 '16 at 17:56
thank you for replying. I wanted to know if there was a batter way to do this. When looking up in a very large table querying database 2 times might increase response time. And there is also a possibility of 2 or more users getting same id from 1st query. and then so they might end up updating same row. — Sulabh Deep Puri, Dec 02 '16 at 18:42

score 8 · Accepted Answer · answered Dec 03 '16 at 13:04

8

You can

add unique indices for user1 and user2 to improve speed and assure integrity.
use Transaction to avoid collisions.

combine the select and update query in one update:

update table match 
set user2 = 'U_2' 
where ((user1 != 'U_2') AND (paired = false)) 
LIMIT 1;

check if the update has affected rows. If not, insert the new row.

If i understand your intension properly, you can also:

remove the column paired, it seems to be redundant, since it is always false when user2=null

answered Dec 03 '16 at 13:04

code_angel

1,537
1
11
21

but both users can be paired for any number of times. And also this way i won't be able to send data from updated row to user requesting match up.. or is there a way for this too? – Sulabh Deep Puri Dec 03 '16 at 19:50
I agree with code_angel's answer. Use update and insert if no affected rows. If update succeeded, just get the information with a select (use a timestamp to get the latest record for that user) OR check [this](http://stackoverflow.com/questions/1388025/how-to-get-id-of-the-last-updated-row-in-mysql) thread to get the id directly from the update. – S. Roose Dec 03 '16 at 20:06
When multiple matches should be allowed, then as S. Roose said, use an additional timestamp column for a match. You can make the unique key over the user1, user2 and timestamp. Or just over user1 and timestamp. It's depend on details. – code_angel Dec 03 '16 at 20:36
tried it and it worked fine, thanx a bunch. I'll accept the answer. – Sulabh Deep Puri Dec 04 '16 at 07:44
Be sure to put both the update and insert in the same transaction. – Rick James Dec 12 '16 at 05:44

score 3 · Answer 2 · answered Dec 06 '16 at 02:23

3

A single statement does one or the other:

INSERT INTO match
    (user1, paired, user2)
    VALUES
    ('U_2', false, 'U_2')   -- either insert this
ON DUPLICATE KEY UPDATE
    user2 = VALUES(user2);  -- or update this

Together with

PRIMARY KEY(user1, paired)  -- a UNIQUE key to control what is "DUPLICATE"

answered Dec 06 '16 at 02:23

Rick James

135,179
13
127
222

Not for this case. There is a many-to-many relation. It was not exactly defined what the OP means with `insert new row.` in the question. But from the context and comments below my answer, it is clear: `both users can be paired for any number of times` – code_angel Dec 08 '16 at 15:02

Update a row if exists otherwise insert

2 Answers2