arielsp arielsp - 2 months ago 16
SQL Question

SQL Server Update Query Not Working

My query is updating all the records in table HumanResources.EmployeePayHistory, instead of updating only those where sick hours <= 20...

Also I realized some fields are being updated more than 1 time.

I don't necessarily have to use this format below, but I do have to use while command to get this done (requirement).

I'm using AdventureWorks2012 database.

DECLARE SickHours CURSOR FOR
select distinct e.BusinessEntityID, p.FirstName, p.LastName, e.JobTitle, e.SickLeaveHours,
round(eph.Rate, 2) as NewHourlyRate
from Person.Person p
inner join HumanResources.Employee e
on p.BusinessEntityID = e.BusinessEntityID
inner join HumanResources.EmployeePayHistory eph
on e.BusinessEntityID = eph.BusinessEntityID
where e.SickLeaveHours <= 20;
OPEN SickHours;
FETCH NEXT FROM SickHours;
WHILE @@FETCH_STATUS = 0
BEGIN
update HumanResources.EmployeePayHistory
set Rate = (Rate * 1.1), ModifiedDate = GETDATE()
FETCH NEXT FROM SickHours;
END;
CLOSE SickHours;
DEALLOCATE SickHours;
GO


How can I fix it?

Thanks

Answer

I noticed that your update statement is lacking a WHERE clause. I think, that is why you are updating ALL records instead of those records where sick hours <= 20. Also, you are NOT initializing the values into variables while you are fetching records, which you should totally do, otherwise using the cursor is an absolute exercise in futility

Since you say that you are REQUIRED to use a cursor, try the query below:

--DECLARE VARIABLES TO STORE CURSOR DATA INTO
DECLARE @BusinessEntityID VARCHAR(250)
DECLARE @FirstName VARCHAR(250)
DECLARE @LastName VARCHAR(250)  
DECLARE @JobTitle VARCHAR(250)
DECLARE @SickLeaveHours INT --CHANGE TYPE AS NEEDED


DECLARE SickHours CURSOR FOR  
select distinct e.BusinessEntityID, p.FirstName, p.LastName, e.JobTitle, e.SickLeaveHours, 
round(eph.Rate, 2) as NewHourlyRate
from Person.Person p 
inner join HumanResources.Employee e 
on p.BusinessEntityID = e.BusinessEntityID
inner join HumanResources.EmployeePayHistory eph
on e.BusinessEntityID = eph.BusinessEntityID
where e.SickLeaveHours <= 20; 
OPEN SickHours;  
FETCH NEXT FROM SickHours INTO @BusinessEntityID, @FirstName, @LastName, @JobTitle, @SickLeaveHours;  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
      update HumanResources.EmployeePayHistory
      set Rate = (Rate * 1.1), ModifiedDate = GETDATE()
      where HumanResources.EmployeePayHistory.BusinessEntityID = @BusinessEntityID --WHERE STATEMENT TO UPDATE THE PROPER ROWS

      FETCH NEXT FROM SickHours INTO @BusinessEntityID, @FirstName, @LastName, @JobTitle, @SickLeaveHours;  
   END;  
CLOSE SickHours;  
DEALLOCATE SickHours;  
GO