我想将用户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');