Link to home
Start Free TrialLog in
Avatar of NDennisV
NDennisVFlag for United States of America

asked on

sql update

I’m having problems UPDATING a table.

I have this query:

SELECT DISTINCT LocationNumber,

dbo.fnCalcDistanceMiles(
tblTerminals.Latitude, tblTerminals.Longitude,
tblLocation.Latitude,  tblLocation.Longitude) as Miles

FROM tblLocation
INNER JOIN tblTerminals ON tblTerminals.TerminalName = tblLocation.Terminal
WHERE tblTerminals.FacilityID IS NOT NULL AND LEN(LTRIM(tblTerminals.FacilityID)) > 0
AND tblLocation.Inactive = 'false'

Which returns the LocationNumber and Miles.

I need to:

UPDATE tblLocation
SET tblLocation.MilesFromTerminal = Miles
WHERE tblLocation.LocationNumber = LocationNumber

But I cannot figure it out. Can someone please tell me how to UPDATE tblLocation?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of NDennisV

ASKER

This works Phillip except it's setting the miles to an integer instead of a decimal.

I need a decimal.

Thank you.
But I had MilesFromTerminal created like this:

MilesFromTerminal      decimal(18, 0)

I changed it to:

MilesFromTerminal      decimal(18, 2)

And it all works.
Good job, thanks.
Avatar of Phillip Burton
Phillip Burton

Two possibilities:

1. MilesFromTerminal is an integer-based field.
2. dbo.fnCalcDistanceMiles returns an integer.

I suspect the former, as you would have noticed if the original query returned integers.

If you are sure neither of the above is true (which I suspect is), then try inserting

* 1.0

at the end of line 6.
Oh my! decimal(18, 0) and decimal(18, 2) takes 9 bytes. That's a lot.

I would suggest using float or decimal(9, 2) instead.
I will. Thanks again.