用一条sql怎么在查询的同时对查询出来的数据进行修改

[a237086617]

用一条sql
查询出出最低员工的工资,并把最低员工的工资进行修改操作给他加500块,最好打个注解

表名 t_user
id name salary

[js14982]

--查询出最低员工
select * from t_user
where salary in ( select min(salary) from t_user);

--修改工资+500
update t_user set salary=salary+500 
where salary in ( select min(salary) from t_user);

[a237086617]

回复谢谢你 出来了

[a237086617]

您知道mysql的咋写吗? mysql的没出来

[ghx287524027]

MERGE INTO t_user ot
USING (select a.*,row_number() over(partition by id order by salary asc) as nu from t_user a) nt
ON (ot.id = nt.id)
WHEN MATCHED THEN UPDATE SET ot.salary = ot.salary +500 WHERE nt.nu=1;

查一下merge into的功能就知道了，

[js14982]

mysql 这条更新语句应该也可以吧？ 你mysql报什么错？

[a237086617]

[SQL] update t_user set salary=salary+500 where salary in ( select min(salary) from t_user); [Err] 1093 - You can't specify target table 't_user' for update in FROM clause

[a237086617]

update t_user set salary=salary+500 where salary in ( select a.salary FROM ( SELECT min(salary) salary from t_user ) a) 以解决