Posted by rdraider on 06/13/06 19:49

The end user is a customer and not familiar with SQL so I was looking for
the least number of steps. I could have created separate scripts or one big
script to create a temp table, populate the table, then update records. My
original script with 7000 lines of update statements seemed the easiest.

Thanks.

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97DDF1D29A419Yazorman@127.0.0.1...
> rdraider (rdraider@sbcglobal.net) writes:
>> Any thoughts on the best way to run an update query to update a specific
>> list of records where all records get updated to same thing. I would
>> think a temp table to hold the list would be best but am also looking at
>> the easiest for an end user to run. The list of items is over 7000
>> Example:
>> update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
>> prod_cat = 'OBS' where item_no = '001-LBK'
>>...
>>
>> All records get set to same. I tried using an IN list but this was
>> significantly slower:
>> update imitmidx_sql set activity_cd = 'O', activity_dt = '20060601',
>> prod_cat = 'OBS'
>> where item_no in
>> ('001-LBK',
>> '001-LYE',
>> '001-XLBK',
>> '001-XLYE',
>> '002-LGR',
>> '002-LRE')
>
> Yes, IN gives bad peformance here. Not so much because of the operation
> itself, but the time it takes to compute the query plan. If you would
> run exactly the same statement again, it would be snap. But obviously
> there is little reason to do that.
>
> Yes, a temp table to hold all the item numbers would be better.
>
> You say that you are looking for something which is easy for an
> end user to run. But does end user run the SQL directly? Is there
> no GUI?
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

[Back to original message]