Cursor through and update database record by looking for value within a range from another table

Question

I have two tables like this:

Table1

Emp_ID    Emp_Name     Age Level
1         Tom          22
2         Susan        50

Table2

Level     Min_Age      Max_Age
Associate   20            30
Senior      31            45

What I want to do is to find which level each employee is at. As such, I want to:

Do for all records in Table1 (All employees) i. Select Level from Table2 where Table1.Age is between Min_Age and Max_Age in Table2 ii.Move to next employee

I have tried the following:

DECLARE @level int, DECLARE @employee_age int
DELCARE  @min_age varcahr int, @max_age varchar int
DECLARE testcursor CURSOR 

LOCAL SCROLL STATIC

FOR

Select Emp_ID FROM Table1

OPEN testcursor 

FETCH NEXT FROM testcursor 

WHILE @@FETCH_STATUS = 0

BEGIN

   FETCH NEXT FROM testcursor

   @min_age=select Min_age from Table2
   @max_age= select Max_age from Table2

   @employee_age=Select age FROM Table1

   @level=select Level from Table2 where 

   update Table1 set level=@level where @employee_age between @min_age and max_age

END

CLOSE cursorName

DEALLOCATE cursorName

This doesn't completely work since I have to cursor through the records in Table2 as well. Any help would be appreciated.

score 1 · Accepted Answer · answered Apr 20 '16 at 15:07

1

You shouldn't have to use a cursor at all. Just join the two tables together.

UPDATE t1
SET t1.[age level] = t2.level
FROM table1 t1
JOIN table2 t2 ON t1.[age level] BETWEEN t2.min_age and t2.max_age

answered Apr 20 '16 at 15:07

SQLChao

7,709
1
17
32

score 0 · Answer 2 · answered Apr 20 '16 at 15:17

You can join- or you can use the following

BEGIN
   for i in (select level, [min age] as MinA, [max age] as MaxA from Table2)
LOOP 
Update Table1
   set Table1.[age level] = i.level
   where Table1.[age level] between i.MinA and i.MaxA;
END LOOP;
END

score 0 · Answer 3 · edited May 23 '17 at 11:52

As suggested by SQLChao, this operation is better done with a set-based approach rather than looping through all the rows and updating them individually.

This is for both performance and readability.

UPDATE t1
SET t1.[level] = t2.level
FROM table1 t1
JOIN table2 t2 ON t1.[age] BETWEEN t2.min_age and t2.max_age

Have a read of this question, one of many on here outlining the reasons for using set-based queries wherever possible: Why are relational set-based queries better than cursors?

Cursor through and update database record by looking for value within a range from another table

3 Answers3