Updating a temp table

Question

Post reply

Updating a temp table

Angelindiego

SSCertifiable

Points: 6048
More actions
April 18, 2006 at 11:26 am

#171290

I have a temp table that I need to update. Can you please tell me if my syntax is correct in the following?? I am getting an error at the keyword 'if' and at the '='. (obviously, something is wrong, but I am not sure how to fix it!)
Thank you!!!!!
update
t
if UserWeightUnit is null
set UserWeightUnit = 'kg', UserWeightFactor = 1
if UserHeightUnit is null
set UserHeightUnit = 'cm', UserHeightFactor = 1
from
@tmp t

Thank you!!,
Angelindiego

Viewing 14 posts - 1 through 14 (of 14 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

I'm sure there is a better way, but you could do it like this...

UPDATE t

set UserWeightUnit = 'kg', UserWeightFactor = 1

FROM @tmp t

WHERE UserWeightUnit is null

UPDATE t

set UserHeightUnit = 'cm', UserHeightFactor = 1

FROM @tmp t

WHERE UserHeightUnit is null

If it needs to happen all at once it could be wrapped in a transaction...

To help us help you read this[/url]For better help with performance problems please read this[/url]

Angelindiego SSCertifiable Points: 6048 More actions · Answer 2

Thanks a ton Luke!! It worked beautifully! Funny, after seeing it this way, it totally makes more sense! Thanks again!!

Thank you!!,

Angelindiego

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

No problem, happy to help.

-Luke

To help us help you read this[/url]For better help with performance problems please read this[/url]

Angelindiego SSCertifiable Points: 6048 More actions · Answer 4

don't leave me just yet!!! I am working on another update....brb!!

Thank you!!,

Angelindiego

Angelindiego SSCertifiable Points: 6048 More actions · Answer 5

Here ya go...show me da magic!!

update t

select @UserWeightFactor = Factor

from UnitConversion

where FromUnit = 'kg'

and ToUnit = UserWeightUnit

set UserWeightFactor = @UserWeightFactor

from

@tmp t

where

UserWeightUnit <> 'cm'

Thank you!!,

Angelindiego

Angelindiego SSCertifiable Points: 6048 More actions · Answer 6

oops, that where clause should read:

where

UserWeightUnit <> 'kg'

thank you again!

Thank you!!,

Angelindiego

Luke L SSC-Dedicated Points: 35547 More actions · Answer 7

I'm not really sure what you're trying to do with this one... Are you trying to Set UserWeightFactor = UnitConversion.Factor WHERE ...?

If so why not something like this... (Btw check this for syntax errors, I'd need more of the table definitions and such to test this.)

UPDATE t

SET t.userweightfact = uc.Factor

FROM @tmp t LEFT JOIN UnitConversion uc ON uc.ToUnit = t.UserWeightUnit

AND uc.FromUnit = 'kg'

WHERE UserWeightUnit <> 'cm'

To help us help you read this[/url]For better help with performance problems please read this[/url]

Angelindiego SSCertifiable Points: 6048 More actions · Answer 8

Thanks Luke! This looks like it should work. I am fussing with it due to an error that says : Server: Msg 446, Level 16, State 9, Line 197

Cannot resolve collation conflict for equal to operation.

Thank you a ton for your help, I do appreciate it!!!

Thank you!!,

Angelindiego

Luke L SSC-Dedicated Points: 35547 More actions · Answer 9

Check out this thread also on Colation issues...

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=252711

heres another one...

http://www.experts-exchange.com/Databases/Q_21048175.html

hope these help.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Angelindiego SSCertifiable Points: 6048 More actions · Answer 10

Off to do some reading!! Thanks again Luke, for all your help!

Thank you!!,

Angelindiego

Angelindiego SSCertifiable Points: 6048 More actions · Answer 11

ok, I am a bit stumped. True, the collation of the columns in UnitConversion are swedish, but in my tmp table, there is no collation...so why a conflict?? Can I set a tmp table up with collation? The variables and the columns are set up as the same data type as the UC table. Any thoughts????

Thank you!!,

Angelindiego

Angelindiego SSCertifiable Points: 6048 More actions · Answer 12

ok, in case anyone is reading this, I was able to get around the error by putting a collation clause after my column in the following manner:

update

t

set t.UserHeightFactor = uc.Factor

from

@tmp t

left join Medicompass31.dbo.UnitConversion uc

on uc.ToUnit = t.UserHeightUnit COLLATE SQL_SwedishStd_Pref_CP1_CI_AS

and uc.FromUnit = 'cm'

this fixed the issue. Thanks again to Luke, you were a great help to me today!!!!

Thank you!!,

Angelindiego

Luke L SSC-Dedicated Points: 35547 More actions · Answer 13

No Problem, Happy to Help.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]