update qry

Question

Post reply

update qry

Tara-1044200

SSCoach

Points: 15951
More actions
January 12, 2010 at 8:57 am

#137205

How do i update a table with prefix number for the existing data ?
example:
ColumnA has "51", I want to update it with "2009.51"

Viewing 15 posts - 1 through 15 (of 24 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

UPDATE TableA

SET ColumnA = '2009.' + ColumnA

WHERE ColumnA = '51'

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

this is not working it is just adding to the existing number say if i have 1 the result is 2010 adding 1 to 2009.

Expected result :
1 = 2009.1
2 = 2009.2
.
.
.
51=2009.52

LutzM SSC Guru Points: 107049 More actions · Answer 3

I actually doubt you've tried the solution Wayne proposed....

He suggested to add a string to a string.

What you describe is to add two numbers.

But when you try to use Waynes statement against an integer column you'd get an error message

Msg 245, Level 16, State 1, Line 9

Conversion failed when converting the varchar value '2009.' to data type int..

But not 2010 like you described...

Would you mind explaining a little more what you're trying to do?

To me it seems like you have a week number and want to add the year as a prefix, separated with a '.'. In this case, shouldn't 2009.1 better be 2009.01?

I have no idea what you want to do with those data but if I'm not completely off with my calendar assumption, then your approach would cause some trouble since '2009.2' would be larger than '2009.19'...

Please provide more details.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Tara-1044200 SSCoach Points: 15951 More actions · Answer 4

I gave my expected result above.

I have a column which has week number like 1 to 52 but i want to update them with prefix year like 2009.1,2009.2......2009.52

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Tara-1044200 (1/12/2010)
I gave my expected result above.
I have a column which has week number like 1 to 52 but i want to update them with prefix year like 2009.1,2009.2......2009.52

What is the data type of the column you attempting to modify? Yes, you gave the expected results, but you didn't provide the table definition.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

Tara-1044200

SSCoach

Points: 15951

January 12, 2010 at 11:52 am

#1102647

data type is real

SQLRNNR SSC Guru Points: 281344 More actions · Answer 7

Tara-1044200 (1/12/2010)
I gave my expected result above.
I have a column which has week number like 1 to 52 but i want to update them with prefix year like 2009.1,2009.2......2009.52

I agree with Lutz's suggestion. Though the week numbers are 1...52 it would be better served to do .01 than .1.

.1

.2

.3

.4

.5

.6

.7

.8

.9

.10 = same as .1

There could arise some data inconsistencies at worst and at best there would be data confusion.

It also appears that the column data type is int. You may want to change it to decimal or varchar.

Knowing the table definition will greatly assist in providing a correct answer.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

update dbo.TheTable set

TheColumn = 2009 + (TheColumn / 100.00)

How about this?

LutzM SSC Guru Points: 107049 More actions · Answer 9

Here's what I came up with to cover the options I think we're faced with...

If the base column is of data tpe INT I actually doubt that it would be possible at all to get a result like 2009.1 ... (so I used NUMERIC(6,2) instead...) 😉

DECLARE @t TABLE (col1 NUMERIC(6,2),col2 varchar(7),col3 varchar(7))

INSERT INTO @t (col1,col2)

SELECT 1,'1' UNION ALL

SELECT 2,'2' UNION ALL

SELECT 3,'3' UNION ALL

SELECT 51,'51' UNION ALL

SELECT 52,'52'

SELECT * FROM @t

UPDATE @t

SET

col1 = 2009 + col1 / 100 ,

col2 = '2009.'+col2,

col3 = '2009.'+ cast(cast(col1 AS int) AS varchar(2))

SELECT * FROM @t

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95461 More actions · Answer 10

lmu92 (1/12/2010)
I actually doubt you've tried the solution Wayne proposed....
He suggested to add a string to a string.

ORIGINAL QUESTION

How do i update a table with prefix number for the existing data ?
example:
ColumnA has "51", I want to update it with "2009.51"

The data was enclosed in quotation marks, so it was assumed that this was a string.

THIS IS WHY it is so important to provide CREATE TABLE statements, and INSERT statements with test data to show what YOUR environment is really like. Read the first link in my signature for how to do that.

Since you have a real data type, I concur with Lynn's suggestion.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 11

WayneS (1/12/2010)
..
The data was enclosed in quotation marks, so it was assumed that this was a string.
...

I agree with you and my comment didn't question your solution at all...

Since we're "left in the dark" regarding data type I decided to use the "curtain fire sample method"...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Tara-1044200 SSCoach Points: 15951 More actions · Answer 12

great, Lynn's query

update dbo.TheTable set
TheColumn = 2009 + (TheColumn / 100.00)

worked for me.

For the same real data type how would i update 2008.1,2008.2......

with 2009.1,2009.2.........

LutzM SSC Guru Points: 107049 More actions · Answer 13

Tara-1044200 (1/12/2010)
great, Lynn's query
update dbo.TheTable set
TheColumn = 2009 + (TheColumn / 100.00)
worked for me.
For the same real data type how would i update 2008.1,2008.2......
with 2009.1,2009.2.........

Would you help me to understand it?

Your requirement was to get 2009.1 when passing 1 to the query.

You're stating that you get your desired result.

But [SELECT 2009 + 1/100.0] will result in 2009.01.

What do I miss here??

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

WayneS SSC Guru Points: 95461 More actions · Answer 14

lmu92 (1/12/2010)
WayneS (1/12/2010)
..
The data was enclosed in quotation marks, so it was assumed that this was a string.
...
I agree with you and my comment didn't question your solution at all...
Since we're "left in the dark" regarding data type I decided to use the "curtain fire sample method"...

Lutz,

Sorry, I forgot to remove the quote from you... I was directing this to the OP.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2