CODEWITHSUNDEEP
sql-servert-sql
hashim malik
hashim malik

Reputation: 19

Update Record of one table While FETCHING value From other table using CURSOR in SQL

I want to update designation_id in table Employee by fetching the value of designation_id from designation table where name of designation in designation table equal to name of designation in employee table Following code is just updating one value using cursor

Upvotes: 0

Views: 793

Answers (1)

SU7
SU7

Reputation: 1756

You are getting only one record in your select. Cursor loops through one row provided and does your intended operation

How Cursors work:

You give the cursor some data by doing some select. Your Cursor now loops through all the records and performs the the operation you intend to do.

Example in SqlServer:

DECLARE Cursor scroll cursor for 

SELECT top 500 ID, demoID, demosecondID
FROM Table  
WHERE condition = @Variable

OPEN Cursor  

fetch next from Cursor into @ID, @demoId,@demosecondId

WHILE @@fetch_status = 0  
    BEGIN  
         -- Your intended operation
         fetch next from Cursor into @ID, @demoId,@demosecondId
    END
Close Cursor  
Deallocate Cursor

As per your requirement, How about using simple join?

UPDATE
    Employee
SET
    Employee.DesignationID = Designation.DesignationID 
FROM
    Employee
INNER JOIN
    Designation
ON 
    Employee.designation= Designation.Name;

Upvotes: 2

Related Questions