Solved

sql update

Posted on 2015-01-30
7
238 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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.
Viewers will learn how the fundamental information of how to create a table.

679 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