Solved

sql update

Posted on 2015-01-30
7
247 Views
Last Modified: 2015-01-30
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
0
Comment
Question by:NDennisV
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40579870
Try this:

UPDATE tblLocation
SET MilesFromTerminal = dbo.fnCalcDistanceMiles(
tblTerminals.Latitude, tblTerminals.Longitude,
tblLocation.Latitude,  tblLocation.Longitude)
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'

Open in new window


To test it, do this code:

 Begin Tran

UPDATE tblLocation
SET MilesFromTerminal = dbo.fnCalcDistanceMiles(
tblTerminals.Latitude, tblTerminals.Longitude,
tblLocation.Latitude,  tblLocation.Longitude)
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'

Select * from tblLocation

Rollback Tran

Open in new window


This will allow you to test the above without it being committed.
0
 

Author Comment

by:NDennisV
ID: 40579897
This works Phillip except it's setting the miles to an integer instead of a decimal.

I need a decimal.

Thank you.
0
 

Author Comment

by:NDennisV
ID: 40579913
But I had MilesFromTerminal created like this:

MilesFromTerminal      decimal(18, 0)

I changed it to:

MilesFromTerminal      decimal(18, 2)

And it all works.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:NDennisV
ID: 40579914
Good job, thanks.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40579916
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.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40579928
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.
0
 

Author Comment

by:NDennisV
ID: 40579991
I will. Thanks again.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question