Problem updating table1 with data from table 2

Hi,

I'm trying to loop through 2 tables and match the records from table1 to table2 and update a couple of columns in table2 if there is a match. The below code updates everyone in table2 with the same data from only 1 record. How can I keep looping through both tables?

USE GradData

GO

Declare @.ssn nvarchar(10)

Declare @.testdesc nvarchar(10)

Declare @.testcode nvarchar(10)

Declare @.testdate datetime

Declare @.testscore nvarchar(50)

Declare @.testsource nvarchar(10)

Declare @.testsortkey nvarchar(10)

Declare @.fcode nvarchar(10)

Declare @.ssn2 nvarchar(10)

Declare @.testdesc2 nvarchar(10)

Declare @.testcode2 nvarchar(10)

Declare @.testdate2 datetime

Declare @.testscore2 nvarchar(50)

Declare @.testsource2 nvarchar(10)

Declare @.testsortkey2 nvarchar(10)

Declare @.fcode2 nvarchar(10)

Declare mycursor Cursor For

Select ssn,testDesc1,testCode1,testDate1,testScore1,testSource1,Fcode1,sortkey

From TestScoresRpt

Open mycursor

FETCH NEXT FROM mycursor

INTO @.ssn,@.testDesc,@.testCode,@.testDate,@.testScore,@.testSource,@.Fcode2,@.testsortkey

Declare mycursor2 Cursor For

Select ssn,test_Desc,test_Code,test_Date,test_Score,test_Source_code,Fcode,test_sort_key

From ScoresAll

Open mycursor2

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

WHILE @.@.FETCH_STATUS = 0

BEGIN

IF @.ssn <> @.ssn2

BEGIN

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

END

IF @.ssn = @.ssn2 and @.testsortkey = @.testsortkey2

BEGIN

FETCH NEXT FROM mycursor2

INTO @.ssn2,@.testDesc2,@.testCode2,@.testDate2,@.testScore2,@.testSource2,@.Fcode2,@.testsortkey2

END

IF @.ssn = @.ssn2 and @.testsortkey <> @.testsortkey2

Update TestScoresRpt

set ssn2=@.ssn2,testDesc2=@.testDesc2,testCode2=@.testCode2,testDate2=@.testDate2,testScore2=@.testScore2,testSource2=@.testSource2,Fcode2=@.fcode2,sortkey2=@.testsortkey2

FETCH NEXT FROM mycursor

INTO @.ssn,@.testDesc,@.testCode,@.testDate,@.testScore,@.testSource,@.Fcode2,@.testsortkey

END

CLOSE mycursor

CLOSE mycursor2

DEALLOCATE mycursor

DEALLOCATE mycursor2

If you are updating all rows where (@.ssn = @.ssn2 and @.testsortkey <> @.testsortkey2), then you would have improved performance using a Set based operation.

Please verify that the end result is two (2) virtually duplicate rows.

|||

THE END RESULT WILL BE A SSN WITH 2 DIFFERENT SORT KEYS FOR EACH SSN IN THE TABLE. WHAT DO YOU MEAN BY SET BASED OPERATION?

THANKS,

|||

My mistake for not completely reading your code.

A Set based operation would be making these updates in one query.

Try something like:

UPDATE t
SET
t.ssn2 = s.ssn,
t.testDesc2=s.testDesc2,
t.testCode2=s.testCode2,
t.testDate2=s.testDate2,
t.testScore2=s.testScore2,
t.testSource2=s.testSource2,
t.Fcode2=s.fcode2,
t.sortkey2=s.testsortkey2
FROM TestScoresRpt t
JOIN ScoresAll s
ON t.ssn = s.ssn
WHERE t.testsortkey <> s.testsortkey2

(This is untested, so please test, test.)