根据以前的select语句更新行

I need to do as many updates as needed based on a select.

我需要根据选择进行尽可能多的更新。

Let's say I have two tables "Groups" and "Members".

假设我有两个表“组”和“成员”。

"Groups"

GroupName  GroupID
Genius     1
Clever     2
Normal     3
Stupid     4
Donkey     5

"Members"

MemberName     GroupID
John           1
Peter          3
Mary           1
Ashley         2
Robin          1
Louis          5
Bill           4
Paul           5

I want to change members from a GroupID to another.

我想将成员从GroupID更改为另一个。

I.e: Members from "Clever" to "Donkey".

即:从“聪明”到“驴”的成员。

select MemberName from Members where GroupID='1';
while($arr = mysqli_fetch_array($rs, MYSQLI_ASSOC))
{
$name = $arr['MemberName'];
}

Then I will update all the selected members into the new group:

然后我将所有选定的成员更新为新组:

$sql .= update Members set GroupID='5' where MemberName='$name';

I know I have to put all names into an array in order to update each one separately, but I'm a bit confused also in the correct update syntax.

我知道我必须将所有名称放入一个数组中,以便分别更新每个名称,但我对正确的更新语法也有点困惑。

2 个解决方案

#1

Why not simply

为什么不简单

UPDATE Members SET GroupID=5 WHERE GroupID=1

Your method would only be required if you had to do some ugly/complicated processing that couldn't be done in SQL (or would be even uglier in SQL).

只有在必须进行一些无法在SQL中完成的丑陋/复杂处理时(或者在SQL中甚至更加丑陋),才需要使用您的方法。

#2

One way you can do this is by following:

您可以通过以下方式实现此目的:

SELECT  @DonkeyGrpID:=GroupID 
FROM Groups 
WHERE GroupName = 'Donkey';

UPDATE Members M, Groups G  
SET M.GroupID = @DonkeyGrpID
WHERE M.GroupID = G.GroupID AND G.GroupName = 'Clever' ;

#1