我想同时更新多个表,所以我使用LEFT JOIN为我的UPDATE创建了一个查询。我尝试了2种方法来进行LEFT JOIN,但都失败了。我不知道我在哪里犯了错误,所以我希望有人能够正确地分析查询并指出错误。
我已经对查询应用了一些格式,这样我看起来就会比以前更可读:
首先是:
"UPDATE "
.
"table1 AS t1 SET t1.Bid = :id "
.
"LEFT JOIN table2 AS t2 SET t2.id = :id ON t1.Bid = t2.id AND t1.status = t2.status "
.
"LEFT JOIN table3 AS t3 SET t3.Bid = :id ON t1.Bid = t3.Bid AND t1.status = t3.status "
.
"LEFT JOIN table4 AS t4 SET t4.id = :id ON t1.Bid = t4.id AND t1.status = t4.status "
.
"LEFT JOIN table5 AS t5 SET t5.Bid = :id ON t1.Bid = t5.Bid AND t1.status = t5.status "
.
"LEFT JOIN table6 AS t6 SET t6.id = :id ON t1.Bid = t6.id AND t1.status = t6.status "
.
"LEFT JOIN table7 AS t7 SET t7.Bid = :id ON t1.Bid = t7.Bid AND t1.status = t7.status "
.
"LEFT JOIN table8 AS t8 SET t8.id = :id ON t1.Bid = t8.id AND t1.status = t8.status "
.
"WHERE t1.Bid = :oldid AND t1.status = :status "
第二个是:
$stmt = $dbh - > prepare("UPDATE "
.
"table1 AS t1 "
.
"LEFT JOIN table2 AS t2 ON t1.Bid = t2.id AND t1.status = t2.status "
.
"LEFT JOIN table3 AS t3 ON t1.Bid = t3.Bid AND t1.status = t3.status "
.
"LEFT JOIN table4 AS t4 ON t1.Bid = t4.id AND t1.status = t4.status "
.
"LEFT JOIN table5 AS t5 ON t1.Bid = t5.Bid AND t1.status = t5.status "
.
"LEFT JOIN table6 AS t6 ON t1.Bid = t6.id AND t1.status = t6.status "
.
"LEFT JOIN table7 AS t7 ON t1.Bid = t7.Bid AND t1.status = t7.status "
.
"LEFT JOIN table8 AS t8 ON t1.Bid = t8.id AND t1.status = t8.status "
.
" SET t1.Bid = :id, "
.
" SET t2.id = :id, "
.
" SET t3.Bid = :id, "
.
" SET t4.id = :id, "
.
" SET t5.Bid = :id, "
.
" SET t6.id = :id, "
.
" SET t7.Bid = :id, "
.
" SET t8.id = :id "
.
"WHERE t1.Bid = :oldid AND t1.status = :status ");
更新
我正在使用第一个选项,我得到:
致命错误:未捕获异常"PDOException",消息为"SQLSTATE[42000]":语法错误或访问冲突:1064您的SQL语法有错误;在第1行的"LEFT JOIN table2 AS t2 SET t2.id="315-512-613-12"附近,查看与MySQL服务器版本相对应的手册,以获得要使用的正确语法
第二个得到:
致命错误:未捕获异常"PDOException",消息为"SQLSTATE[42000]":语法错误或访问冲突:1064您的SQL语法有错误;查看与MySQL服务器版本对应的手册,了解在第1行的"SET t2.id=‘315-512-613-123V’,SET t3.Bid=‘315-512-613-123V’,SE’附近使用的正确语法
I had it working using this query.
"UPDATE
table1 AS t1 LEFT JOIN
table2 AS t2 ON t1.Bid = t2.id AND t1.status = t2.status LEFT JOIN
table3 AS t3 ON t1.Bid = t3.Bid AND t1.status = t3.status LEFT JOIN
table4 AS t4 ON t1.Bid = t4.id AND t1.status = t4.status LEFT JOIN
table5 AS t5 ON t1.Bid = t5.Bid AND t1.status = t5.status LEFT JOIN
table6 AS t6 ON t1.Bid = t6.id AND t1.status = t6.status LEFT JOIN
table7 AS t7 ON t1.Bid = t7.Bid AND t1.status = t7.status LEFT JOIN
table7 AS t8 ON t1.Bid = t8.id AND t1.status = t8.status
SET t1.Bid = :id,
t2.id = :id,
t3.Bid = :id,
t4.id = :id,
t5.Bid = :id,
t6.id = :id,
t7.Bid = :id,
t8.id = :id
WHERE t1.Bid = :oldid
AND t1.status = :status "