January 8, 2012 at 4:46 am
i have the table1 with sample rows
Create Table Table1 (
KeyID int,
col1 int,
col2 int,
col3 int,
col4 int,
col5 int
)
insert table1
select 1,1111,2222,3333,4444,5555 union
select 2,1111,2222,3333,4444,5555 union
select 3,0,1111,3333,4444,5555 union
select 4,2222,3333,4444,5555,6666 union
select 5,1111,2222,4444,5555,0 union
select 6,1111,0,3333,4444,7777 union
select 6,7777,3333,6666,1111,4444
SELECT * FROM TABLE1 WHERE (
col1 = 1111 or
col2 = 1111 or
col3 = 1111 or
col4 = 1111 or
col5 = 1111)
I need to update the table with 9999 where any column that hold value 1111
How to do i that?
Thanks in advance,
January 8, 2012 at 5:55 am
Easiest to do it in 5 update statements
UPDATE TABLE1 SET Col1 = 9999 where Col1=1111
UPDATE TABLE1 SET Col2 = 9999 where Col2=1111
UPDATE TABLE1 SET Col3 = 9999 where Col3=1111
UPDATE TABLE1 SET Col4 = 9999 where Col4=1111
UPDATE TABLE1 SET Col5 = 9999 where Col5=1111
p.s. I hope those aren't the real names of the table and columns
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 8, 2012 at 6:17 am
What she means by that is that if your table is really huge there's another solution which will scan the whole table only once rahter than 5 times (assuming no adequate indexes on those 5 columns).
UPDATE dbo.Table
SET Col1 = CASE WHEN Col1 = 1111 THEN 9999 ELSE Col1 END,
Col2 = CASE WHEN Col12= 1111 THEN 9999 ELSE Col2 END,
...
WHERE 1111 IN (Col1, Col2, Col3, Col4, Col5)
This assumes that the 5 columns are not nullable. If they are you can use ISNULL(Col1, -1), ...
January 8, 2012 at 12:17 pm
Thanks Gila & Ninja. Actually, this is sample table and the original table has got 47 columns and 26000 rows.
So far, i wrote a SP that accept old value and new value for update. In the SP, i am updating old value with newvalue using case statement. I was looking for any better solution. My current solution looks like this
Create proc Table1_update (old_val int, new_val int)
as
update Table1 set
col1 = CASE WHEN col1 = old_val then new_val else col1 end,
col2 = CASE WHEN col2 = old_val then new_val else col2 end,
col3 = CASE WHEN col3 = old_val then new_val else col3 end,
col4 = CASE WHEN col4 = old_val then new_val else col4 end,
col5 = CASE WHEN col5 = old_val then new_val else col5 end
WHERE (col1 = old_val OR col2 = old_val OR col3 = old_val OR
col4 = old_val OR col5 = old_val)
January 8, 2012 at 12:22 pm
You can do that (which will pretty much scan the table) or 5 separate updates (as I showed), but there's no real other alternatives
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply