Can anyone write this query without a cursor?

Question

I need to make this query without a cursor, but how?

My boss said not to use cursors, why not? How can this be written without one?

I created a table called tbl_StudentCoverage that has SSN varchar(9) and fklogin int.

I also created a table Mstr_tbl_login that has an intID int that is a foreign key to fklogin in tbl_StudentCoverage, and strFour varchar(4).

I want to update strFour in Mstr_tbl_login with the last four of the SSN in tbl_StudentCoverage matching intID to fklogin.

I'm thinking my boss doesn't know SQL or he doesn't understand what I need to do. I consider myself an experienced SQL person.

USE mstr_Database
GO

DECLARE @fkLogin int
DECLARE @ssn varchar(9)


DECLARE NW4 CURSOR FOR
SELECT fkLogin, SSN FROM mstr.tbl_StudentCoverage

OPEN NW4

FETCH NEXT FROM NW4
INTO @fkLogin, @ssn

WHILE (@@FETCH_STATUS = 0)
BEGIN
        IF (SELECT intID FROM mstr.mstr_tbl_Login WHERE intID = @fkLogin) = @fkLogin
    BEGIN
        UPDATE mstr.mstr_tbl_Login SET strFour = right(@ssn,4) WHERE intID = @fkLogin
    END
        FETCH NEXT FROM NW4
        INTO @fkLogin, @ssn
END

print 'done'
CLOSE NW4
DEALLOCATE NW4

GO

No errors, this works perfect. Just my boss wants no cursors.

Welcome to Stack Overflow! Since you consider yourself an experienced SQL person, perhaps you should describe what you have written to your boss and that it works. — Étienne Laneville, Oct 08 '19 at 23:12
Before we even get to the cursor, this: `IF (SELECT intID FROM mstr.mstr_tbl_Login WHERE intID = @fkLogin) = @fkLogin` is a convoluted way if saying `if 1=1` — Nick.Mc, Oct 08 '19 at 23:15
Unless there is no matching intID for fklogin is why I do that. — SQL_Mstr08, Oct 08 '19 at 23:22
OK you're right, I've never seen it done that way before, for good reason IMHO. So in the first instance you could move that 'exists' logic in to your cursor driving query. Then after you do that you might discover that you can combine your `UPDATE` statement and your cursor driving query into one simple `UPDATE` statement without a cursor. — Nick.Mc, Oct 08 '19 at 23:25
Cursors are necessary sometimes. Every time that they *aren't* necessary, they are almost universally and massively inefficient (compared to set operations). I do not see any reason why a cursor would be necessary to do what your code is doing. — avery_larry, Oct 09 '19 at 03:12

score 3 · Answer 1 · answered Oct 08 '19 at 23:44

3

You're advanced? Try this.

update
    A
set
    A.strFour = right(B.ssn,4)
from
    mstr.mstr_tbl_Login A
join
    mstr.tbl_StudentCoverage B
on
    B.fklogin = A.intId

answered Oct 08 '19 at 23:44

ManOfOceans

31
1

1

If you need further explanation Google "SQL Server update join". Here are a few pertinent results. https://stackoverflow.com/questions/982919/sql-update-query-using-joins http://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/ – Isaac Oct 08 '19 at 23:47

Can anyone write this query without a cursor?

1 Answers1