在一个查询中使用update和select子句

I have two tables like below .

我有两张桌子如下。

circle_user

user_id    circle_id     user_type_id
1            1              1
2            1              2
3            1              2

user_type
id    type
1     admin
2     member

I have to implement switch admin concept . If a user deletes his account and leaves his circle , new admin must be assigned to that circle. for example in the given circle_user table if user_id = 1 deletes his account , than next member of that circle must become the admin of that circle. So i need a query which will select the very next member of that circle and update the user_type_id from 2 to 1 .

我必须实现切换管理概念。如果用户删除了他的帐户并离开了他的圈子,则必须为该圈子分配新的管理员。例如,在给定的circle_user表中,如果user_id = 1删除了他的帐户,则该圈子的下一个成员必须成为该圈子的管理员。所以我需要一个查询,它将选择该圈子的下一个成员并将user_type_id从2更新为1。

I need a result like.

我需要一个像这样的结果。

circle_user

user_id    circle_id     user_type_id
1            1              1
2            1              1
3            1              2

How can i do this in one query? I have circle_id = 1 as Parameter .

我如何在一个查询中执行此操作?我有circle_id = 1作为参数。

1 个解决方案

#1

I am not sure what exactly you are asking for. But just as the first iteration:

我不确定你究竟要求的是什么。但就像第一次迭代一样:

UPDATE circle_user
  INNER JOIN (
    SELECT MIN(user_id) circle_id
    FROM circle_user 
    GROUP BY circle_id
    HAVING SUM(IF(user_type_id=1,1,0))=0
  ) cu
  ON circle_user.circle_id = cu.circle_id
SET circle_user.user_type_id = 1