dbo.MatchFirstName(@FirstName) and dbo.MatchLastName(@LastName)
are table Value Functions(TVFs).
Problem:
In my Update statement i.e Update #Temp after Insert, I want only those rows to be updated that were inserted via the Insert statement used just above it i.e In first Update statment, only the rows that were inserted by selecting from TVF dbo.MatchFirstNumber.
I have my SQL queries like this:
select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchFirstName'
Insert into #Temp2(Rownumber, ValFromFunc) select RowNumber,PercentMatch from dbo.MatchFirstName(@FirstName)
Update #Temp2 set FuncWeight = @constVal, Percentage = ValFromFunc * @constVal
select * from #Temp2
select @constVal = FunctionWeight from dbo.FunctionWeights where FunctionWeights.FunctionName = 'MatchLastName'
Insert into #Temp2(Rownumber, ValFromFunc) select RowNumber,PercentMatch from dbo.MatchLastName(@LastName)
Update #Temp2 set FuncWeight = @constVal, Percentage = ValFromFunc * @constVal
select * from #Temp2
It as of now obviously updates all the rows.
Secondly, I tried with the condition where FuncWeight is null i.e
Update #Temp2 set FuncWeight = @constVal, Percentage = ValFromFunc * @constVal where FuncWeight is null
but surprisingly it results in all 1's in FuncWeight Column. What could be the reason?
What could be the workaround for this problem?
Aucun commentaire:
Enregistrer un commentaire