?
Solved

sql update

Posted on 2015-01-30
7
Medium Priority
?
256 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 2000 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

770 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