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.

Answer 1

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