Post reply

can this update statement be made easier

  • March 3, 2013 at 7:55 pm

    #269270

    I am running an update statement as follows

    UPDATE dbo.BigTable

    SET BigTable.Software_Version_Raw =

    ( CASE

    WHEN dbo.BigTable.Software_Version_Raw LIKE '1.%' THEN '1.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '2.%' THEN '2.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '3.%' THEN '3.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '4.%' THEN '4.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '5.%' THEN '5.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '6.%' THEN '6.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '7.%' THEN '7.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '8.%' THEN '8.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '9.%' THEN '9.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '10.%' THEN '10.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '11.%' THEN '11.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '12.%' THEN '12.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '13.%' THEN '13.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '14.%' THEN '14.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '15.%' THEN '15.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '16.%' THEN '16.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '17.%' THEN '17.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '18.%' THEN '18.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '19.%' THEN '19.x'

    WHEN dbo.BigTable.Software_Version_Raw LIKE '20.%' THEN '20.x'

    WHEN dbo.BigTable.Software_Version_Raw = '1' THEN '1.x'

    WHEN dbo.BigTable.Software_Version_Raw = '2' THEN '2.x'

    WHEN dbo.BigTable.Software_Version_Raw = '3' THEN '3.x'

    WHEN dbo.BigTable.Software_Version_Raw = '4' THEN '4.x'

    WHEN dbo.BigTable.Software_Version_Raw = '5' THEN '5.x'

    WHEN dbo.BigTable.Software_Version_Raw = '6' THEN '6.x'

    WHEN dbo.BigTable.Software_Version_Raw = '7' THEN '7.x'

    WHEN dbo.BigTable.Software_Version_Raw = '8' THEN '8.x'

    WHEN dbo.BigTable.Software_Version_Raw = '9' THEN '9.x'

    WHEN dbo.BigTable.Software_Version_Raw = '10' THEN '10.x'

    WHEN dbo.BigTable.Software_Version_Raw = '11' THEN '11.x'

    WHEN dbo.BigTable.Software_Version_Raw = '12' THEN '12.x'

    WHEN dbo.BigTable.Software_Version_Raw = '13' THEN '13.x'

    WHEN dbo.BigTable.Software_Version_Raw = '14' THEN '14.x'

    WHEN dbo.BigTable.Software_Version_Raw = '15' THEN '15.x'

    WHEN dbo.BigTable.Software_Version_Raw = '16' THEN '16.x'

    WHEN dbo.BigTable.Software_Version_Raw = '17' THEN '17.x'

    WHEN dbo.BigTable.Software_Version_Raw = '18' THEN '18.x'

    WHEN dbo.BigTable.Software_Version_Raw = '19' THEN '19.x'

    WHEN dbo.BigTable.Software_Version_Raw = '20' THEN '20.x'

    ELSE dbo.BigTable.Software_Version_Raw

    END

    )

    is there an easier way to do this EG search through the string and look for the full stop something like if it says

    version 10.3.6.1 just search for the first full stop and make it say 10.x

    any help would be appreciated

  • March 3, 2013 at 8:23 pm

    #1592851

    alan

    Try something like this

    create table #SoftwareTest

    (

    SoftVersion varchar(500),

    SoftVersionUpdated varchar(500)

    )

    insert into #SoftwareTest (SoftVersion)

    Select '10.78.5'

    union

    Select '1.78'

    union

    Select '2'

    union

    Select '4.5'

    union

    Select '20.5'

    union

    Select '4'

    union

    Select '8.63.0'

    union

    Select '10'

    union

    Select '4.56.89'

    update #SoftwareTest set SoftVersionUpdated=

    case when CHARINDEX('.',SoftVersion,0) >0 then

    substring(softversion,0, CHARINDEX('.',SoftVersion,0) )+ '.X'

    else SoftVersion + '.X' end

    select * from #SoftwareTest

    drop table #SoftwareTest

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • March 3, 2013 at 8:24 pm

    #1592852

    Heh... dang it. Roshan Joe beat me to it. You can, however, make it one notch simpler. Not sure which will be faster, though.

    UPDATE dbo.BigTable

    SET Software_Version_Raw = LEFT(Software_Version_Raw,CHARINDEX('.',Software_Version_Raw+'.')-1)+'.x'

    Seems like a real shame to lose all that version info, though. Seems like it would be better to add a calculated column to the table to do the N.x thing and keep the original information.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 3, 2013 at 8:35 pm

    #1592854

    Jeff Moden (3/3/2013)

    Heh... dang it. Roshan Joe beat me to it. You can, however, make it one notch simpler. Not sure which will be faster, though.

    UPDATE dbo.BigTable

    SET Software_Version_Raw = LEFT(Software_Version_Raw,CHARINDEX('.',Software_Version_Raw+'.')-1)+'.x'

    Seems like a real shame to lose all that version info, though. Seems like it would be better to add a calculated column to the table to do the N.x thing and keep the original information.

    Hey Jeff, I am nowhere near your talent and reputation... 🙂

    Thanks for showing a simpler way. The OP will be happy for this simpler solution.

    I this this will perform better since without the case statement.

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • March 3, 2013 at 8:48 pm

    #1592855

    thanks guys thats very helpful, just one thing, the second query you provided how does that cover anything else just to provide the original value

  • March 3, 2013 at 9:00 pm

    #1592857

    We both wrote the examples with assumptions that you have only two patterns, if not you can go with the case stament itself

    create table #SoftwareTest

    (

    SoftVersion varchar(500),

    SoftVersionUpdated varchar(500)

    )

    insert into #SoftwareTest (SoftVersion)

    Select '10.78.5'

    union

    Select '1.78'

    union

    Select '2'

    union

    Select '4.5'

    union

    Select '20.5'

    union

    Select '4'

    union

    Select '8.63.0'

    union

    Select '10'

    union

    Select '4.56.89'

    union

    Select 'NA'

    union

    select Null

    update #SoftwareTest set SoftVersionUpdated=

    case when CHARINDEX('.',SoftVersion,0) >0 then

    substring(softversion,0, CHARINDEX('.',SoftVersion,0) )+ '.X'

    when isnumeric(softversion)=1 then

    SoftVersion + '.X'

    else softversion end

    select * from #SoftwareTest

    drop table #SoftwareTest

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • March 3, 2013 at 9:11 pm

    #1592858

    ok guys I have gone with the case statement it seems to be working but I am getting quite a few columns with the value

    unknown.x in it even the the original has 10.6.1 or 8.1.4 etc

    could it be because of some problem with the original column

    I have software_version_raw set as (Varchar(max),null)

  • March 3, 2013 at 9:23 pm

    #1592859

    alan_lynch (3/3/2013)

    ok guys I have gone with the case statement it seems to be working but I am getting quite a few columns with the value

    unknown.x in it even the the original has 10.6.1 or 8.1.4 etc

    could it be because of some problem with the original column

    I have software_version_raw set as (Varchar(max),null)

    I don't see how that's possible in SQL Server. "unknown" isn't something that SQL Server will populate on its own.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 3, 2013 at 9:48 pm

    #1592862

    ok guys I think I best show you the full query to see where I am going wrong. OH bare in mind some of the values in the columns have 1.x.x or 8.xx.x these seem to be the ones that cause the problem

    Here is the query

    use TestData

    go

    drop table BigTable

    drop table RefTable

    SELECT *

    INTO RefTable

    FROM dbo.The_Big_Kahuna

    order by dbo.The_Big_Kahuna.Software_Name_Raw

    SELECT *

    INTO BigTable

    FROM dbo.The_Big_Kahuna

    order by dbo.The_Big_Kahuna.Software_Name_Raw;

    SET NOCOUNT ON

    BEGIN TRANSACTION Inner2;

    GO

    update dbo.BigTable set BigTable.Software_Version_Raw =

    case

    when CHARINDEX('.',Software_Version_Raw,0) >0 then

    substring(Software_Version_Raw,0, CHARINDEX('.',Software_Version_Raw,0) )+ '.x'

    when isnumeric(Software_Version_Raw)=1 then

    Software_Version_Raw + '.x'

    else

    Software_Version_Raw + '.x'

    end

    go

    COMMIT TRANSACTION Inner2;

    drop table ResultTable

    SELECT *

    INTO ResultTable

    FROM dbo.BigTable

    order by dbo.BigTable.Software_Name_Raw;

    select * from ResultTable

    where software_name_raw like '%office%'

    order by dbo.ResultTable.Software_Name_Raw

  • March 3, 2013 at 10:02 pm

    #1592868

    I am still not clear why your update is not working. One thing, your case statement should be changed

    update dbo.BigTable set BigTable.Software_Version_Raw =

    case

    when CHARINDEX('.',Software_Version_Raw,0) >0 then

    substring(Software_Version_Raw,0, CHARINDEX('.',Software_Version_Raw,0) )+ '.x'

    when isnumeric(Software_Version_Raw)=1 then

    Software_Version_Raw + '.x'

    else

    Software_Version_Raw end

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • March 4, 2013 at 3:56 pm

    #1593203

    I figured out why it was because of the data I was importing.

    I have used that query thank you very much for your assistance

Viewing 11 posts - 1 through 10 (of 10 total)

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