mysql - 连接更新时收到错误，语法不正确

Question

我在 MySQL 中收到以下 SQL 错误，我不确定原因。我是 MySQL 新手。

UPDATE I
SET I.user_comments = DD.value
FROM
redcap_user_information I
    JOIN redcap_data D ON D.value = I.user_email AND D.project_id = 439 AND D.field_name = 'email'
    JOIN redcap_data DD ON DD.record = D.record AND DD.project_id = 439 AND DD.field_name = 'irb'
WHERE
user_comments IS NULL
    AND allow_create_db = 1
    AND user_suspended_time IS NULL
ORDER BY I.user_email

错误是

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM
 redcap_user_information I
     JOIN redcap_data D ON D.value = I.use'

有人可以透露一些信息吗？

Answer 1

不确定连接部分的逻辑，但使用连接进行更新的正确语法如下

update redcap_user_information I
JOIN redcap_data D ON D.value = I.user_email AND D.project_id = 439 AND D.field_name = 'email'
JOIN redcap_data DD ON DD.record = D.record AND DD.project_id = 439 AND DD.field_name = 'irb'
set I.user_comments = DD.value
WHERE
user_comments IS NULL --- use the table alias name to access the column 
AND allow_create_db = 1 --- use the table alias name to access the column
AND user_suspended_time IS NULL --- use the table alias name to access the column