We are using MySQL where we have deals table that contains references to two users stored in two columns User_id1 & User_id2. There is no guaranty in which order those are stored. For example the records below represent a deal between two unique pair of users

id ... User_id1 User_id2
-- --- -------- --------
 1 ...       11       20
 2 ...       20       11
 3 ...      116     3459
 4 ...       20       11

We need an SQL query to migrate this data into two new tables below


chat_id user_id
------- -------
      1      11
      1      20
      2     116
      2    3459



For each unique pair of users in deals table, we need to create a unique conversation record in Chat_rooms table and add those two users as participants to the Participants table with the reference to the created Chat_room ID

I'm hopping to do this with SQL, potentially using some temporary tables.

1 Answer 1

CREATE VIEW uniqueness (id, User_id1, User_id2)
       LEAST(User_id1, User_id2) User_id1,  
       GREATEST(User_id1, User_id2) User_id2
GROUP BY LEAST(User_id1, User_id2),  
         GREATEST(User_id1, User_id2);

and then

INSERT INTO Chat_rooms (id) SELECT id FROM uniqueness;


INSERT INTO Participants (chat_id, user_id)
SELECT id, User_id1 FROM uniqueness
SELECT id, User_id2 FROM uniqueness;

You may create temporary table instead of view

-- or --

You may insert view's source text into INSERT queries

  • I don't think chat_id is supposed to originally come from the deals table (the reference to which, by the way, is missing from the view). From the data samples in the question, it looks more like they are just new IDs assigned arbitrarily across all the unique pairs. Other than that, I completely agree with the approach.
    – Andriy M
    Commented Dec 19, 2021 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.