update qry

Question

update qry

Viewing 9 posts - 16 through 24 (of 24 total)

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

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

lmu92 (1/12/2010)
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??

Basically:

2009. + 1 = 2009.1

2009. + 10 = 2009.10

Both values are numerically the same and the column is defined as a real value. This meant that 1 - 9 needed to be preceded by a 0.

WayneS SSC Guru Points: 95461 More actions · Answer 2

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.........

That would be:

UPDATE dbo.TheTable

SET TheColumn = TheColumn + 1

WHERE TheColumn >= 2008

AND TheColumn < 2009

Out of curiousity... these are extremely basic questions you're asking solutions for. What is your level of proficiency on SQL?

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 3

Lynn Pettis (1/12/2010)
...
Basically:
2009. + 1 = 2009.1
2009. + 10 = 2009.10
Both values are numerically the same and the column is defined as a real value. ...

Completely understood. But that doesn't answer the question why running your query

[2009 + (TheColumn / 100.00)] would result in 2009.1 for TheColumn = 1. It would work for TheColumn = 10 though...

Your point has been brought up before, yet unanswered by Tara.

Your statement

This meant that 1 - 9 needed to be preceded by a 0.

does not comply with Taras requirement to get 2009.1,2009.2 for 1 and 2... (see several posts before), yet Tara replies that your solution works. That's what confuses me.

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]

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

If we were working with character data, then we could have done 2009.1 and 2009.10. However, working with real (numeric) data we actually had modify the requirement and precede the values 1 - 9 with a 0 or it wouldn't work as needed, we' essentially have two 2009.1's and if this is a unique field it would fail.

As was pointed out, her original post was somewhat misleading since she hadn't provided the table definition for the table. That would have shown us upfront that what she wanted would not have worked as she was thinking, 2009. + 1 and 2009. + 10 would result in the same values for two records.

LutzM SSC Guru Points: 107049 More actions · Answer 5

Just for the sake of it (since the business reason is still a secret to me):

DECLARE @TheColumn INT

SET @TheColumn = 1

SELECT left((2009 + @TheColumn / CASE WHEN @TheColumn <10 THEN 10.0 ELSE 100.0 END),5+len(@TheColumn))

/* result set (character format):

2009.1 for @TheColumn = 1

2009.10 for @TheColumn = 10

*/

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 6

Thank you so much guys.. appreciate all your replies for my post.

As Lynn said actually it was my fault not giving the datatype in the post but anyways this helped me much. Thank you

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

lmu92 (1/12/2010)
Just for the sake of it (since the business reason is still a secret to me):
DECLARE @TheColumn INT
SET @TheColumn = 1
SELECT left((2009 + @TheColumn / CASE WHEN @TheColumn <10 THEN 10.0 ELSE 100.0 END),5+len(@TheColumn))
/* result set (character format):
2009.1 for @TheColumn = 1
2009.10 for @TheColumn = 10
*/

We are updating the column with the results of the "concatenation". Since the field is real (numeric) 2009.1 and 2009.10 are the same.

LutzM SSC Guru Points: 107049 More actions · Answer 8

Lynn Pettis (1/12/2010)
We are updating the column with the results of the "concatenation". Since the field is real (numeric) 2009.1 and 2009.10 are the same.

It's still an insoluble puzzle to me:

either you have a numeric data type, then it's impossible to get the desired output for 1 and 10 (2009.1 and 2009.10 rsp) or

you have a charater data type then it's impossible to succesfully apply the solution you provided.

I just asked for clarification by Tara how she could qualify a solution as "worked for me" if the desired output cannot achieved one way or the other.

Anyway, a happy OP counts more than a logical solution... 😉

Never mind, I'll stop whining/complainig.

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]

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

lmu92 (1/12/2010)
Lynn Pettis (1/12/2010)
We are updating the column with the results of the "concatenation". Since the field is real (numeric) 2009.1 and 2009.10 are the same.
It's still an insoluble puzzle to me:
either you have a numeric data type, then it's impossible to get the desired output for 1 and 10 (2009.1 and 2009.10 rsp) or
you have a charater data type then it's impossible to succesfully apply the solution you provided.
I just asked for clarification by Tara how she could qualify a solution as "worked for me" if the desired output cannot achieved one way or the other.
Anyway, a happy OP counts more than a logical solution... 😉
Never mind, I'll stop whining/complainig.

No, no, no. I think we are crossing paths without understanding each other.

Yes, OP asked for "2009.1", "2009.2", ..., '2009.10", ...

Problem, OP failed to tell us that the datatype of the column was real (numeric). If this had been know up front, we could have told the OP that this was not possible unless we padded the numbers 1 - 9 with a leading zero, or they would have to change the datatype of the column.

Once we knew the datatype of the column, the OP's original request is no longer viable with the existing datatype and an alternative solution needed to be provided.

That is what I provided, and the OP is happy, as you noted.