SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


can this update statement be made easier


can this update statement be made easier

Author
Message
Oracle765
Oracle765
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 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
Roshan Jospeh
Roshan Jospeh
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3231 Visits: 2093
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223462 Visits: 42003
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Roshan Jospeh
Roshan Jospeh
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3231 Visits: 2093
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
Oracle765
Oracle765
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 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
Roshan Jospeh
Roshan Jospeh
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3231 Visits: 2093
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
Oracle765
Oracle765
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 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)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)SSC Guru (223K reputation)

Group: General Forum Members
Points: 223462 Visits: 42003
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Oracle765
Oracle765
SSC-Addicted
SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)SSC-Addicted (486 reputation)

Group: General Forum Members
Points: 486 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
Roshan Jospeh
Roshan Jospeh
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3231 Visits: 2093
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search