Omesh Omesh - 4 months ago 7
SQL Question

Update , Select in one SQL statement

I have two tables

First Table

create table ApprovedLeave(
Username varchar(100),
FromDate date,
ToDate date,
type varchar(100),
address varchar(1000),
contactNo varchar(20),
NoofWorkingDays int,
);


Second Table

create table EMPLOYEE(
FName varchar(100),
LName varchar(200),
Username varchar(100),
NoOfDaysRemaining int,
constraint emp1 primary key(Username));


Once the leave is approved
NoOfDaysRemaining = NoOfDaysRemaining- NoofWorkingDays

SO far what I have doesn't perform the above operation.

create Procedure UpdateNoOfDays2
AS
BEGIN
update le
set NoOfDaysRemaining = al.sum_NoofWorkingDays
from EMPLOYEE le join
(select UserName, sum(NoofWorkingDays) as sum_NoofWorkingDays
from ApprovedLeave
group by UserName
) al
on le.UserName = al.UserName
End


Please help. Thank you in advance

Answer

Your question is missing a question, but based on this:

NoOfDaysRemaining = NoOfDaysRemaining- NoofWorkingDays

Are you wanting this? SET NoOfDaysRemaining = NoOfDaysRemaining - al.sum_NoofWorkingDays

UPDATE le
SET    NoOfDaysRemaining = NoOfDaysRemaining - al.sum_NoofWorkingDays
FROM   EMPLOYEE le 
INNER JOIN
         (SELECT   UserName, sum(NoofWorkingDays) as sum_NoofWorkingDays
          FROM     ApprovedLeave
          GROUP BY UserName
         ) al ON le.UserName = al.UserName
Comments