iterate, compare and update all records in 3 colum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

iterate, compare and update all records in 3 colum

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-08-19 : 17:35:22

I need some help making this work please

Trying to normalize some data

right now I use a select to get the comparison(its a function created) and display the percent match in a colum hits1, hits2, hits3

I want to update with the following conditions
--If hits1 > 85 then update PickAt = @param1Orig
--If hits2 > 85 then update LOADUNLOAD = @param1Orig
--If hits3 > 85 then update DeliverTo = @param1Orig

Maybe putting this into a tmp table then working with it?

so for each field(PickAt, LOADUNLOAD, DeliverTo) iterate through records and
set as @param1Orig
then compare it to all records in PickAt, LOADUNLOAD, DeliverTo
where the percentage(hits) is greater than 85 Update it with the @param1Orig


Declare @param1 As nvarchar(max)
Declare @param1Orig As nvarchar(max)
Declare @State As nvarchar(2)

Set @param1Orig = 'DYN, INC., 1 MAE GROVE RD, GEORGETOWN  2136' 

SET @param1 =(SELECT [dbo].[BOA_CleanString](@param1))
SELECT [dbo].[BOA_Compare](@param1, val1) as hits1, PickAt, hits2, LoadUnload, hits3, DeliverTo 
FROM
(
SELECT [dbo].[BOA_Compare](@param1, val1) as hits1, 
[dbo].[BOA_Compare](@param1, val2) as hits2,
[dbo].[BOA_Compare](@param1, val3) as hits3,
val1, PickAt, LoadUnload, DeliverTo 
FROM 
(SELECT [dbo].[BOA_CleanString]([PickAt]) AS val1,
[dbo].[BOA_CleanString]([LoadUnload]) AS val2,
[dbo].[BOA_CleanString]([DeliverTo]) AS val3,
 PickAt, LoadUnload, DeliverTo FROM dbo.DetroitLanes_formatted)t
)r
WHERE hits1 > '50' OR hits2 > '50' OR hits3 > '50'


--If hits1 > 85 then update PickAt = @param1Orig
--If hits2 > 85 then update LOADUNLOAD = @param1Orig
--If hits3 > 85 then update DeliverTo = @param1Orig

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources