如何更新表中的数据？ [英] How to update data in table?

问题描述

我有以下表格

I have following tables

create table emp
(
	empid int primary key identity(1,1),
	name varchar(50)
)

create table rolemaster
(
	roleid int primary key identity(1,1),
	[role] varchar(50)
)

create table map
(
	empid int foreign key references emp(empid),
	roleid int foreign key references rolemaster(roleid),
)

create table sal
(
	empid int foreign key references emp(empid),
	salary float
)

我想更新其roleid的员工的薪水是1比20％，在单一更新声明中更新roleid为2乘30％的员工的工资。 Plz帮帮我。



我尝试了什么：



update sal设置salary = case empid

when（从地图中选择empid，其中roleid =（从rolemaster选择roleid，其中role ='a'））然后工资* 0.2

时（选择empid来自地图，其中roleid =（从rolemaster中选择roleid，其中role ='c'））然后工资* 0.5

结束



但是不能正常工作。

I want to update salary of employee whose roleid is 1 by 20% and update salary of employee whose roleid is 2 by 30% in single update statement. Plz help me.

What I have tried:

update sal set salary=case empid
when (select empid from map where roleid=(select roleid from rolemaster where role='a')) then salary*0.2
when (select empid from map where roleid=(select roleid from rolemaster where role='c')) then salary*0.5
end

but did not work.

推荐答案

你可以使用UPDATE和JOIN来做到这一点。



检查以下内容 -

You can use UPDATE with JOIN to do this.

Check something like following-

UPDATE S SET S.salary=S.salary+(S.salary*(CASE WHEN R.[role]='a' THEN 0.2 WHEN R.[role]='c' THEN 0.3 ELSE 0 END))
FROM sal S
INNER JOIN map M ON S.empid=M.empid
INNER JOIN rolemaster R ON R.roleid=M.roleid

我没有执行它，因此可能需要进行微小的更改。如果它没有用，请告诉我。



谢谢

I have't executed it so minor changes may be required. Please let me know in case it doesn't help.

Thanks

试试这个

Try this

update s set s.salary=(case when r.role='a' then s.salary*1.2 when r.role='c' then s.salary*1.5 end) from salary s inner join rolemaster r on r.roleid=s.roleid inner join  map m on s.empid=m.empid

谢谢

Thanks

这篇关于如何更新表中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！