Click here to Skip to main content
15,867,834 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
customer table (CUST_ID,CUST_NAME,CUST_ADDRESS,EMP_ID)

Employee table(EMP_ID,MANAGER_ID,EMP_NAME,EMP_TEL,EMP_SALARY,HIRE_DATE,DEPENDENT_NAME)


The salary of employee dealing with customer having id=4 has been doubled


What I have tried:

update employee
set emp_salary=emp_salary*2
from employee e
join customer c on c.emp_id=e.emp_id
having c.cust_id=4;
Posted
Updated 2-Mar-20 7:11am
Comments
Maciej Los 2-Mar-20 13:07pm    
What's your question?
Abed Al Rahman Hussien Balhawan 2-Mar-20 13:09pm    
The salary of employee dealing with customer having id=4 has been doubled
Maciej Los 2-Mar-20 13:16pm    
You've got what you wanted (written).
Abed Al Rahman Hussien Balhawan 2-Mar-20 13:19pm    
code not working :(

1 solution

Something like this?
SQL
UPDATE employee
SET employee.emp_salary = employee.emp_salary * 2.0
WHERE EXISTS (SELECT c.EMP_ID FROM customer c WHERE c.emp_id = employee.emp_id)


[Edit]
SQL
UPDATE employee
SET employee.emp_salary = employee.emp_salary * 2.0
WHERE EXISTS (SELECT c.EMP_ID FROM customer c WHERE c.emp_id = employee.emp_id AND  c.cust_id=4)


I don't know Oracle, so the above is a general solution. E.g. with MS SQL you can use left join in updates which would be probably more performant.
 
Share this answer
 
v3
Comments
Abed Al Rahman Hussien Balhawan 2-Mar-20 13:15pm    
i am afraid not , only the employee who is working with cust_id=4 must be doubled
i tryed this
update employee
set emp_salary=emp_salary*2
from employee e
inner join customer c on c.emp_id=e.emp_id
having c.cust_id=4
[no name] 2-Mar-20 13:23pm    
Ohh yes, sorry I forgot that one (id == 4). I think it is easy for you to add this restriction?
Abed Al Rahman Hussien Balhawan 2-Mar-20 13:28pm    
i tried , i am still doing something wrong somewhere, can you show me how u do it ?
[no name] 2-Mar-20 13:33pm    
I will try to help, see [Edit] in answer; not sure I understood it in the right way. Anyway, would be nice next time you show what you have tried and what failed...

Btw. HAVING will be used in SQL statements using GROUP BY. I suggest you (don't take this as offense) to read some basic SQL tutorials ;)
Abed Al Rahman Hussien Balhawan 2-Mar-20 13:36pm    
to be honest with you i just tried it the same as u written it, but i forgot to close the parentheses. my mistake , much 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