lundi 26 janvier 2015

Update a set of SQL statements



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