SQL Help

  • 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,

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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), ...

  • 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)

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply