php - SQL多表更新问题

Question

我正在尝试同时更新两个表。操作成功完成。但是，它会更新表中的所有记录，而不是指定的记录。我试过建议here ，但无济于事。任何想法可能是什么问题？我的代码如下所示。

$sql = "UPDATE $receiverTable, $currUserTable 
SET $currUserTable.originator = '$curr_username', 
$receiverTable.originator = '$curr_username', 
$currUserTable.status = '$currValue', 
$receiverTable.status = '$currValue' 
WHERE ($receiverTable.username = '$curr_username') 
OR ($currUserTable.username = '$curr_username')";

Answer 1

您的 JOIN 没有条件(您真的不应该再用逗号编写 JOIN)，因此每一行都与其他每一行 JOIN，这意味着它们都有一个 JOIN到其中一个 WHERE 条件为真的行，因此它们都得到更新。用适当的条件重写您的 JOIN，问题就会消失。像这样的东西:

UPDATE $receiverTable
JOIN $currUserTable ON $currUserTable.somecolumn = $receiverTable.somecolumn
SET $currUserTable.originator = '$curr_username', 
$receiverTable.originator = '$curr_username', 
$currUserTable.status = '$currValue', 
$receiverTable.status = '$currValue' 
WHERE ($receiverTable.username = '$curr_username') 
OR ($currUserTable.username = '$curr_username')

如果无法连接表，则需要将 UPDATE 编写为两个单独的查询。