我想将用户id 1的年级别的值从tbl_profile更改为“ 3rd”,并且我想从表中引用名称“ captain America”,因为他们具有相同的user_id。

这是我的示例表:

tbl_profile
users_id    year_level
1           none

tbl_usersinfo
users_id        full_name
1           captain america


这是我的查询:

UPDATE tbl_profile AS p
SET p.year_level = '3rd'
LEFT JOIN
tbl_usersinfo AS i
ON
i.users_id = p.users_id
WHERE
i.full_name = 'captain america';


我希望它像这样,但是我知道不可能执行此查询,因为我没有指定full_name列的来源:

UPDATE tbl_profile AS p
SET p.year_level = '3rd'
WHERE i.full_name = 'captain america'
LEFT JOIN
tbl_usersinfo AS i
ON
i.users_id = p.users_id

最佳答案

使用此查询:

UPDATE tbl_profile
SET year_level='3rd'
WHERE users_id IN (
  SELECT users_id
  FROM tbl_usersinfo WHERE full_name = 'captain america');

09-25 22:00