Soner B Soner B - 3 months ago 8
MySQL Question

MySQL SELECT all datas and UPDATE in one query

I have 3 table.
One of them store users_data.

I run this sql code:

SELECT @rownum := @rownum + 1 AS position,
user_id, score, user_type
FROM users_data
ORDER BY score DESC


and result like this:

enter image description here

and i have 2 table for users like this

That my tables

users1

+----+----------+----------+
| id | username | position |
+----+----------+----------+
| 1 | uname | 0 |
| 2 | uname2 | 0 |
| 3 | uname3 | 0 |
| 5 | uname5 | 0 |
| 11 | uanme11 | 0 |
| 12 | uname12 | 0 |
+----+----------+----------+

users0

+-----+----------+----------+
| id | username | position |
+-----+----------+----------+
| 1 | uname | 0 |
| 111 | uname111 | 0 |
| 138 | uname138 | 0 |
| 241 | uname241 | 0 |
+-----+----------+----------+


I want to update users position while SELECT sql running in one query.

if user_type is 0, update users0.position = @rownum
if user_type is 1, update users1.position = @rownum


result must be like this:

users1

+----+----------+----------+
| id | username | position |
+----+----------+----------+
| 1 | uname | 1100 |
| 2 | uname2 | 1100 |
| 3 | uname3 | 1075 |
| 5 | uname5 | 1075 |
| 11 | uanme11 | 1075 |
| 12 | uname12 | 1175 |
+----+----------+----------+

users0

+-----+----------+----------+
| id | username | position |
+-----+----------+----------+
| 1 | uname | 1075 |
| 111 | uname111 | 1025 |
| 138 | uname138 | 1025 |
| 241 | uname241 | 1025 |
+-----+----------+----------+

Answer
update users1,users0
set users1.position = (
            select v.position
            from
            (SELECT 
            @rn1 := @rn1 + 1 AS position,
            users_id, score, user_type
            FROM (select @rn1:=0) rn, users_data ud
            ORDER BY score DESC
            ) v
            where v.users_id = users1.id and v.user_type = 1
            ) 
,
     users0.position = (
        select s.position
        from
        (SELECT 
        @rn := @rn + 1 AS position,
        users_id, score, user_type
        FROM (select @rn:=0) rn, users_data ud
        ORDER BY score DESC
        ) s
        where s.users_id = users0.id and s.user_type = 0
        ) 

where 1 = 1
;

result

+-----+------+----------+----------+
| src | id   | username | position |
+-----+------+----------+----------+
| u1  |   12 | uname12  |        1 |
| u1  |    1 | uname    |        2 |
| u1  |    2 | uname2   |        3 |
| u1  |   11 | uanme11  |        4 |
| u1  |    5 | uname5   |        5 |
| u1  |    3 | uname3   |        6 |
| u0  |    1 | uname    |        7 |
| u0  |  111 | uname111 |        8 |
| u0  |  138 | uname138 |        9 |
| u0  |  241 | uname241 |       10 |
+-----+------+----------+----------+
10 rows in set (0.00 sec)