Click here to Skip to main content
15,888,351 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

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

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