mysql - 如果值是从另一个表中选择的，则更新表

Question

我有两个表:rec_new_license 和recruit_zips。

recruit_zips 包含“zip_code”和“office_name”，并包含邮政编码和办公室名称。

rec_new_license 包含字段“zip”和字段“recruit_office”。如果“zip”中的值与recruit_zips 表中的“zip_code”匹配并且“office_name”与“Spring Hill”匹配，则需要更新此表的“recruit_office”字段。

查询均未执行任务或因错误而失败。我在这件事上做错了吗？

$sql = "UPDATE rec_new_license 
SET recruit_office = 'Spring Hill' 
WHERE zip IN 
( 
 SELECT zip_code FROM recruit_zips 
 WHERE office_name = 'Spring Hill'
)";

$results = $mysqli->query($sql);

还尝试过:

$sql = "UPDATE rec_new_license t1
  JOIN recruit_zips t2
    ON t1.zip = t2.zip_code
    WHERE t2.office_name = 'Spring Hill'
SET t1.recruit_office = 'Spring Hill'
";

Answer 1

你的语法错误。它必须是:

$sql = "UPDATE rec_new_license t1
  JOIN recruit_zips t2
    ON t1.zip = t2.zip_code
    SET t1.recruit_office = 'Spring Hill'
    WHERE t2.office_name = 'Spring Hill'
";