Solved

sql update

Posted on 2015-01-30
7
233 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Steps to produce a data strategy 5 43
Query / Window function ? 3 18
SQL Pivot add row totals 2 13
Common Records between Sub Queries 4 0
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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 to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

895 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now