mysql - 根据第三个表连接 MySQL 的结果，用一个查询更新两个表

Question

我需要使用一个查询更新两个表，具体取决于与另一个表连接的结果。

我有一个 tblPermutations 需要对照 tblDictionary 进行检查，并且匹配的那些需要标记为已处理并插入第三个 tblSpellathon。

我尝试了以下但没有成功。

UPDATE tblspellathon AS s, 
(SELECT p.*
FROM tblpermutations p
INNER JOIN tbldictionary AS d ON d.Word = p.Permu AND p.`Language` = d.Lang
WHERE d.correct = -1 AND p.PermuProcessed = 0) AS p
SET p.PermuProcessed = -1, s.DerWord = p.Permu, s.WordListID = p.WordListID;

和

UPDATE tblspellathon AS s, tblpermutations AS p, tbldictionary AS d
SET p.PermuProcessed = -1, s.DerWord = p.Permu, s.WordListID = p.WordListID
WHERE p.permu = d.Word AND p.`Language` = d.Lang AND d.correct = -1 AND 
p.PermuProcessed = 0;

在上述两个查询中，我得到：

SQL Error (1205): Lock wait timeout exceeded; try restarting transaction 
Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 0 of 1 query: 0.000 sec.

谢谢

标签： mysqlsqlsql-update