SQL连接更新另一行为2的行 - SQL join to update a row where the other row has 2?

SQL join to update a row where the other row has 2?

本文关键字：一行的行连接更新 SQL | 更新日期: 2023-09-27

我试过了:

UPDATE tbl_questions 
JOIN (SELECT tbl_users.uid, tbl_users.is_spam
FROM tbl_users JOIN tbl_questions ON tbl_users.uid = tbl_questions.uid
)
SET question_type_id = 2
WHERE is_spam = 2;

我想将这些问题question_type_id更新为2，其中用户表is_spam = 2。我的sql写错误Every derived table must have its own alias…

tbl_questions => uid, question_type_id,
tbl_users => uid, is_spam,

子查询必须有别名。

UPDATE tbl_questions 
JOIN 
(
   SELECT tbl_users.uid, tbl_users.is_spam
   FROM tbl_users 
   JOIN tbl_questions ON tbl_users.uid = tbl_questions.uid
) sub_query_alias on sub_query_alias.uid = tbl_questions.uid
SET question_type_id = 2
WHERE is_spam = 2;

我在上面的查询中使用了sub_query_alias。更简单的查询是

UPDATE tbl_questions q
JOIN tbl_users u ON u.uid = q.uid AND u.is_spam = 2
SET q.question_type_id = 2

请检查:-

UPDATE t1 SET t1。question_type_id = 2 from tbl_questions AS t1连接tbl_users作为t1.tbl_questions上的t2。Uid = t2。uid WHERE t2。