Ask for an UPDATE statement

<code>table_user

id            user            shangjiaid         vip
1           用户A            2                    0
2           用户B            3                    0
3           用户C            2                    0
4           用户D            5                    0
5           用户E            5                    0
6           用户F            5                    0
7           用户G            2                    0
8           用户H            5                    0
9           用户J            2                    0
10          用户K            5                    0
11          用户K            5                    0
12          用户K            7                    0
13          用户K            12                    0


更新VIP等于1


比如 id=5的这个人  下家的数量满足3个  然后更新vip=1

用一句sql能update更新？  这个应该属于递归...



就是说每个人都有上家...如果这个人下家满足3个人..然后更新VIP=1


update结果集

table_user

id            user            shangjiaid         vip
5           用户E            5                    1
2           用户B            3                    1</code>

Reply content:

<code>table_user

id            user            shangjiaid         vip
1           用户A            2                    0
2           用户B            3                    0
3           用户C            2                    0
4           用户D            5                    0
5           用户E            5                    0
6           用户F            5                    0
7           用户G            2                    0
8           用户H            5                    0
9           用户J            2                    0
10          用户K            5                    0
11          用户K            5                    0
12          用户K            7                    0
13          用户K            12                    0


更新VIP等于1


比如 id=5的这个人  下家的数量满足3个  然后更新vip=1

用一句sql能update更新？  这个应该属于递归...



就是说每个人都有上家...如果这个人下家满足3个人..然后更新VIP=1


update结果集

table_user

id            user            shangjiaid         vip
5           用户E            5                    1
2           用户B            3                    1</code>

update table_user set vip = 1 where id in (select * from (SELECT a.shangjiaid FROM table_user as a GROUP BY a.shangjiaid having count(1) >= 3) as b)

I feel like there should be a better solution. .

Another suggestion, when Liz asks questions in the future, it is best to post the table creation statements and data filling statements. This will save a lot of time. Maybe there will be more answers.

update table set vip=1 where id=(select shangjiaid from (select from table group by shangjiaid having count()>2)t ) This is probably the idea, the sql may be wrong, you can see for yourself

You can separate such complex statements. In my opinion, this complex SQL statement completed in one step is not good. When the amount of data is large, the business will be particularly stuck.
You can first count which IDs have more than 3 merchant IDs, and then update the VIP to 1 respectively; if it is not possible, you can also add a queue, which should be better for the business.