update where condition - SQL Server Forums

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)

update where condition

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-08-20 : 10:34:50

i want to update a record where a condition is met

Update table
Set Column1 = @param1Orig
WHERE Exists(--use a query and some functions to compare each row in colum
compared value > 90)???
--this was my best guess. below is the actual code i am trying and the query has been running for 23 minutes.. not good, no error



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

Set @param1Orig = 'HUL CM, 73 KRK RD, BERD-DO 60 BL, MIDND' 

SET @param1 =(SELECT [dbo].[BOA_CleanString](@param1Orig))
Update dbo.DetroitLanes_formatted
Set LoadUnload = @param1Orig
WHERE Exists(
SELECT hits2, LoadUnload
FROM
(
SELECT [dbo].[BOA_Compare](@param1, val2) as hits2,
val2, LoadUnload
FROM 
(SELECT [dbo].[BOA_CleanString]([LoadUnload]) AS val2,
  LoadUnload FROM dbo.DetroitLanes_formatted)t
)r
WHERE  hits2 > '90' 
)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-20 : 14:08:53

Your EXISTS statement has no correlation to the instance of the table you're updating. All the rows in the table will be updated to the same value - assuming the exists statement returns anything. You may want to stop that query

Be One with the Optimizer
TG

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-08-20 : 15:07:24

quote:
Originally posted by TG

Your EXISTS statement has no correlation to the instance of the table you're updating. All the rows in the table will be updated to the same value - assuming the exists statement returns anything. You may want to stop that query

Be One with the Optimizer
TG

how can I fix? :(

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-08-20 : 15:38:11

Perhaps this:
correlate on whatever the Primary Key, Unique Constraint, or Logical Key column(s) are in this table.


update df set
       df.LoadUnload = @param1Orig
from   (
       SELECT [dbo].[BOA_Compare](@param1, val2) as hits2
              ,val2
              ,LoadUnload
              ,<PrimaryKeyColumn(s)>
       FROM   (
              SELECT [dbo].[BOA_CleanString]([LoadUnload]) AS val2
                     ,LoadUnload 
              FROM dbo.DetroitLanes_formatted
              )t
       WHERE  [dbo].[BOA_Compare](@param1, val2) > '90' 
       ) as d
inner join dbo.DetroitLanes_formatted df
       on df.<PrimaryKeyColumn(s)> = d.<PrimaryKeyColumn(s)>

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources