获取使用更新查询"from"的错误

Question

select email,test_status,candidate_id,user_id from candidate_tests,candidate_candidates,users where candidate_tests.candidate_id=candidate_candidates.id and candidate_candidates.user_id=users.id;
update candidate_tests set candidate_tests.test_status='completed' from users as U inner join candidate_candidates as cc on candidate_candidates.user_id=users.id  inner join candidate_tests as ct on  candidate_tests.candidate_id=candidate_candidates.id where users.email='[e-mail]';

错误是：from 不是此位置的有效输入。此查询有什么问题？

Answer 1

您的更新语法错误。它必须是：

update candidate_tests  as ct  inner join candidate_candidates as cc
 on  ct.candidate_id=cc.id
inner join  users as U 
 on cc.user_id=U.id  and  u.email='[e-mail]'
set candidate_tests.test_status='completed'

Answer 2

阅读 UPDATE 和 JOIN 语法参考：https://dev.mysql.com/doc/refman/5.7/en/update.htmlhttps://dev.mysql.com/doc/refman/5.7/en/join.html

您将联接放在错误的位置

        UPDATE users as U 
    INNER JOIN candidate_candidates as cc 
            ON candidate_candidates.user_id=users.id 
    INNER JOIN candidate_tests as ct 
            ON candidate_tests.candidate_id=candidate_candidates.id 
           SET candidate_tests.test_status='completed' 
         WHERE users.email='[e-mail]';

Answer 3

无需在更新查询中使用"from"。请将您的查询更改为

update candidate_tests  as ct  inner join candidate_candidates as cc
 on  ct.candidate_id=cc.id
inner join  users as U 
 on cc.user_id=U.id  and  u.email='[e-mail]'
set candidate_tests.test_status='completed'

参考