I have created an inbox system. Signed in user can message other signed in users. The usermessage Table from database has two fields 1. userid and 2. messageid. The following is an excerpt from this table.
userid | messageid
12 | 1
13 | 1
14 | 2
15 | 2
12 | 3
15 | 3
12 | 4
14 | 4
In above situation when user '12' send message to user '13'. I want the messageid '1' to move to bottom of the table so that when the user '12' or '13' is checking his mailbox, the messageid '1' needs to appear on the top of inbox as a recent conversation. So far I have been able to achieve this by deleting the messageid '1' and inserting as a new query for each userid. This is my code:
DELETE FROM usermessage WHERE userid = '12' and messageid = '1';
INSERT INTO usermessage SET userid = '12', messageid = '1';
DELETE FROM usermessage WHERE userid = '13' and messageid = '1';
INSERT INTO usermessage SET userid = '13', messageid = '1';
Is it possible get this done with one query. So far, I was not able to find any definite answer in stackflow related to my problem.