0

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

Participants

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

Chat_rooms

id
--
 1
 2

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.

0
CREATE VIEW uniqueness (id, User_id1, User_id2)
AS
SELECT MIN(id), 
       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;

and

INSERT INTO Participants (chat_id, user_id)
SELECT id, User_id1 FROM uniqueness
UNION ALL
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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