Refresh the line if it exists, otherwise insert

Question

I have a table 'match' like

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

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

This db is connected to a server on which several users can try to connect, so I need to find the best possible solution that speeds it up so that it does not lock the table for a long time.

the solution that 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 indexes for user1 and user2 to increase speed and maintain integrity.
• Use a transaction to avoid collisions.

• Combine the selection and update request 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 a new line.

If I understand your intent correctly, you can also:

• delete the paired column, it seems superfluous since it is always false when user2=null

Answer 2

One 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"