Click here to Skip to main content
15,906,645 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have following tables

SQL
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
)


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.
Posted
Updated 25-Apr-16 23:29pm

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
 
Share this answer
 
You can use UPDATE with JOIN to do this.

Check something like following-
SQL
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
 
Share this answer
 
Comments
Animesh Datta 26-Apr-16 5:38am    
My 5!
Suvendu Shekhar Giri 26-Apr-16 5:40am    
Thanks :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900