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.)
