user3009344 user3009344 - 7 months ago 17
SQL Question

Update query using select data

take a look at my table first please:

[user_id, row_id, row_value] - table: users_data

Content:

row_id 1:



[1, 1, 1]
[2, 1, 2]
[3, 1, 2]
[4, 1, 2]


row_id 2:



[1, 2, 1]
[2, 2, 1]
[3, 2, 1]
[4, 2, 1]


row_id 3:



[2, 3, 5]
[2, 3, 5]
[2, 3, 5]


I would like to find out if it is possible to do those 3 steps only by using MySQL language automatically:

Step 1: Get list of
row_id
of user_id = 1(so [1, 1, 1] and [1, 2, 1])

Step 2: Iterate through that list to find out what are most popular row_values JUST for row_ids that are in the list gained from Step 1

Step 3: Iterate through result of Step 2 to make row_values of user_id = 1, be just the same as most popular row_values.

Now thats the queries i have made:

Step 1:

SELECT row_id FROM users_data WHERE user_id = 1;


Result of that query are 2 rows:


  • [1]

  • [2]



Step 2: So now from those 2 rows, somehow 2 queries should be run(filled with data of Step 1):

SELECT row_id, row_value FROM users_data WHERE row_id = 1 GROUP BY row_value ORDER BY COUNT(row_value) LIMIT 1;


SELECT row_id, row_value FROM users_data WHERE row_id = 2 GROUP BY row_value ORDER BY COUNT(row_value) LIMIT 1;


Result of 1st query:

[1, 2]

Result of 2nd query:

[2, 1]

Step 3: And finally from those 2 rows, i would like to update user 1 row_values. So something like this should be run:

UPDATE users_data SET row_value = 2 WHERE user_id = 1 AND row_id = 1;


UPDATE users_data SET row_value = 1 WHERE user_id = 1 AND row_id = 2;


So i would like those steps to be done automatically(data for step 3 taken from step 2, data from step 2 taken from step 1) but i do not know how to make it(now i can just write it in programming language or fill queries with notepad).

Here are the queries in SQLFiddle: http://sqlfiddle.com/#!9/8d5594/14

Answer

Not sure what you are doing, but this seems to do that:

UPDATE users_data u_old
JOIN (
    SELECT DISTINCT u1.user_id, u2.row_id, u2.row_value
    FROM users_data u1
    JOIN users_data u2 USING(row_id)
    WHERE u1.user_id = 1
        AND (u2.row_id, u2.row_value) = (
            SELECT u3.row_id, u3.row_value 
            FROM users_data u3
            WHERE u3.row_id = u2.row_id
            GROUP BY u3.row_id, u3.row_value
            ORDER BY COUNT(u3.row_value) DESC
            LIMIT 1
        )
) u_new USING (user_id, row_id)
SET u_old.row_value = u_new.row_value

http://sqlfiddle.com/#!9/5fd6b/2

Note: In step 2 you are using GROUP BY row_value. This way you might get unexpected results, because you are also selecting row_id, which is not in the GROUP BY list. So i changes that to GROUP BY u3.row_id, u3.row_value.

Comments