Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » can this update statement be made easier
Add to briefcase
11 posts, Page 1 of 2
12»»

can this update statement be made easier Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
alan_lynch
Posted Sunday, March 03, 2013 7:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:38 PM
Points: 50, Visits: 88
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
joeroshan
Posted Sunday, March 03, 2013 8:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:45 AM
Points: 572, Visits: 1,157
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
Jeff Moden
Posted Sunday, March 03, 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 @ 8:21 PM
Points: 32,893, Visits: 26,765
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1426064
joeroshan
Posted Sunday, March 03, 2013 8:35 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:45 AM
Points: 572, Visits: 1,157
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
alan_lynch
Posted Sunday, March 03, 2013 8:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:38 PM
Points: 50, Visits: 88
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
joeroshan
Posted Sunday, March 03, 2013 9:00 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:45 AM
Points: 572, Visits: 1,157
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
alan_lynch
Posted Sunday, March 03, 2013 9:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:38 PM
Points: 50, Visits: 88
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
Jeff Moden
Posted Sunday, March 03, 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 @ 8:21 PM
Points: 32,893, Visits: 26,765
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1426072
alan_lynch
Posted Sunday, March 03, 2013 9:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 11:38 PM
Points: 50, Visits: 88
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
joeroshan
Posted Sunday, March 03, 2013 10:02 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 3:45 AM
Points: 572, Visits: 1,157
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
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.