NDennisV
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.Fac ilityID)) > 0
AND tblLocation.Inactive = 'false'
Which returns the LocationNumber and Miles.
I need to:
UPDATE tblLocation
SET tblLocation.MilesFromTermi nal = Miles
WHERE tblLocation.LocationNumber = LocationNumber
But I cannot figure it out. Can someone please tell me how to UPDATE tblLocation?
Thanks
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.Fac
AND tblLocation.Inactive = 'false'
Which returns the LocationNumber and Miles.
I need to:
UPDATE tblLocation
SET tblLocation.MilesFromTermi
WHERE tblLocation.LocationNumber
But I cannot figure it out. Can someone please tell me how to UPDATE tblLocation?
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
But I had MilesFromTerminal created like this:
MilesFromTerminal decimal(18, 0)
I changed it to:
MilesFromTerminal decimal(18, 2)
And it all works.
MilesFromTerminal decimal(18, 0)
I changed it to:
MilesFromTerminal decimal(18, 2)
And it all works.
ASKER
Good job, thanks.
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.
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 would suggest using float or decimal(9, 2) instead.
ASKER
I will. Thanks again.
ASKER
I need a decimal.
Thank you.