Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

can this update statement be made easier Expand / Collapse
Author
Message
Posted Sunday, March 3, 2013 7:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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
Post #1426061
Posted Sunday, March 3, 2013 8:23 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:50 AM
Points: 617, Visits: 1,276
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
Custom cleanup script for backups
Post #1426063
Posted Sunday, March 3, 2013 8:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 37,107, Visits: 31,664
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1426064
Posted Sunday, March 3, 2013 8:35 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:50 AM
Points: 617, Visits: 1,276
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
Custom cleanup script for backups
Post #1426066
Posted Sunday, March 3, 2013 8:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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
Post #1426068
Posted Sunday, March 3, 2013 9:00 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:50 AM
Points: 617, Visits: 1,276
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
Custom cleanup script for backups
Post #1426070
Posted Sunday, March 3, 2013 9:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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)
Post #1426071
Posted Sunday, March 3, 2013 9:23 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 37,107, Visits: 31,664
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1426072
Posted Sunday, March 3, 2013 9:48 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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
Post #1426075
Posted Sunday, March 3, 2013 10:02 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:50 AM
Points: 617, Visits: 1,276
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
Custom cleanup script for backups
Post #1426081
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse