mysql pdo使用LEFT JOIN更新记录

Question

"UPDATE tablename1  AS t1 LEFT JOIN
tablename2 AS t2 ON t1.pin = t2.pin AND t1.status = t2.status LEFT JOIN
tablename3 AS t3 ON t1.pin = t3.pin AND t1.status = t3.status LEFT JOIN
tablename4 AS t4 ON t1.pin = t4.pin AND t1.status = t4.status LEFT JOIN
tablename5 AS t5 ON t1.pin = t6.pin AND t1.status = t6.status LEFT JOIN
tablename6 AS t6 ON t1.pin = t6.pin AND t1.status = t6.status LEFT JOIN
SET t1.status = : status,
    t2.status = : status,
    t3.status = : status,
    t4.status = : status,
    t5.status = : status,
    t6.status = : status
WHERE t1.pin = : pin
AND t1.status = : active "

这是我同时更新多个表的查询。有些表可能是空的，所以我使用 LEFT JOIN这样我只用值更新表。但我得到了这个错误near 'SET t1.status = 'NotActive' AND t2.status = 'N' at line 7'所以我所做的就是改变 ,至AND当我搜索错误 Uncaught exception 'PDOException' with message 'SQLSTATE[42000] 时，我仍然收到错误我发现的大多数事情都是当有 reserved word 时出现。 。就我而言，我的代码中没有保留字。

欢迎提出任何建议

Answer 1

UPDATE tablename1  AS t1 LEFT JOIN
tablename2 AS t2 ON t1.pin = t2.pin AND t1.status = t2.status LEFT JOIN
tablename3 AS t3 ON t1.pin = t3.pin AND t1.status = t3.status LEFT JOIN
tablename4 AS t4 ON t1.pin = t4.pin AND t1.status = t4.status LEFT JOIN
tablename5 AS t5 ON t1.pin = t6.pin AND t1.status = t6.status LEFT JOIN
tablename6 AS t6 ON t1.pin = t6.pin AND t1.status = t6.status LEFT JOIN ????
SET t1.status = : status,
    t2.status = : status,
    t3.status = : status,
    t4.status = : status,
    t5.status = : status,
    t6.status = : status
WHERE t1.pin = : pin
AND t1.status = : active

最后一个 LEFT JOIN 是什么？？？它是否留在 SET 部分？

我认为您在这一行上也有一个拼写错误:

tablename5 AS t5 ON t1.pin = t6.pin AND t1.status = t6.status LEFT JOIN

我认为你的意思更接近于此:

UPDATE tablename1  AS t1
LEFT JOIN tablename2 AS t2 ON t1.pin = t2.pin AND t1.status = t2.status 
LEFT JOIN tablename3 AS t3 ON t1.pin = t3.pin AND t1.status = t3.status 
LEFT JOIN tablename4 AS t4 ON t1.pin = t4.pin AND t1.status = t4.status 
LEFT JOIN tablename5 AS t5 ON t1.pin = t5.pin AND t1.status = t5.status 
LEFT JOIN tablename6 AS t6 ON t1.pin = t6.pin AND t1.status = t6.status
SET t1.status = : status,
    t2.status = : status,
    t3.status = : status,
    t4.status = : status,
    t5.status = : status,
    t6.status = : status
WHERE t1.pin = : pin
AND t1.status = : active