CODEWITHSUNDEEP
mysqlsql
Sulabh Deep Puri
Sulabh Deep Puri

Reputation: 324

Update a row if exists otherwise insert

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.

Upvotes: 7

Views: 2198

Answers (2)

Rick James
Rick James

Reputation: 142208

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"

Upvotes: 4

code_angel
code_angel

Reputation: 1537

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

Upvotes: 8

Related Questions