Update only certain rows.

[YOUNGCODGER]

Hi,

I have to update a row in a database table if it matches one of n values (approx 500 out of 10,000). The statement would be of the form ‘update table set table. rate = ’12.5’ where table.cust_id = ‘xxx’. ‘xxx’ is one of the 500 values. Obviously I do not want to write 500 update statements or a huge ‘or’ list. How can I tackle this?

Many thanks,

YoungCodger

 

[gmmastros]

Do you have the list of 500 values in a table somewhere?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[YOUNGCODGER]

Not in a database table but easily put into a text, csv or WHY file.

 

[gmmastros]

If you have it in a file, then it would be pretty simple to put it in a table, right?

Here are 2 methods you may find interesting. One method uses in which allows you to use a comma separated list of values. The second method uses an inner join with a table that ONLY contains cust_id's you want changed.

Declare @Temp Table(Cust_id VarChar(20), Rate Decimal(10,2))

Insert Into @Temp Values('a', 1)
Insert Into @Temp Values('b', 2)
Insert Into @Temp Values('c', 3)
Insert Into @Temp Values('d', 4)
Insert Into @Temp Values('e', 5)

Update @Temp
Set    Rate = 12.5
Where  Cust_Id In ('a','d')

Select * From @Temp

Declare @CustIdList Table(Cust_ID VarChar(20))

Insert Into @CustIdList Values('a')
Insert Into @CustIdList Values('c')
Insert Into @CustIdList Values('e')

Update T
Set    T.Rate = 99.9
From   @Temp T
       Inner Join @CustIdList C
          On T.Cust_Id = C.Cust_Id

Select * From @Temp

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom