January 9, 2013 at 9:01 am
Hi all
Can anyone advise please, I have a TSQL 2008r2 headache...
I'm calculating the geo distance between a previous geo point and current geo point.
The code below, as a SELECT statement, returns 1.1 M rows and seems to be good.
When run as an UPDATE it sets all 1.8 M rows and with a zero value :ermm:
WITH tblCTE AS -- create common table expression
(SELECT
Row_Number() OVER (ORDER BY varVehicleReg, dteEvent) AS RowNumber
,dteEvent, varVehicleReg, geoPoint, fltDistanceMoved
FROM stageCoAvlsLog WHERE intFix > 0 -- all geoPoint are populated.
)
SELECT Cur.dteEvent, Cur.varVehicleReg, Cur.fltDistanceMoved,
-- or ...
--UPDATE stageCoAvlsLog SET fltDistanceMoved =
Cur.geoPoint.STDistance(Prv.geoPoint)-- AS fltMetres
FROM tblCTE AS Cur
LEFT OUTER JOIN tblCTE AS Prv
ON Cur.RowNumber = Prv.RowNumber + 1 -- join on current and previous row
AND Cur.varVehicleReg=Prv.varVehicleReg
-- the table defs are
[dteEvent] [datetime] NOT NULL,
[varVehicleReg] [varchar](32) NOT NULL,
[geoPoint] [geography] NULL,
[fltDistanceMoved] [real] NULL,
The http://msdn.microsoft.com/en-us/library/ms177523(v=sql.105).aspx says "Only the rows returned by the common table expression are modified." Hmm... 1 issue or 2?
Dave
January 9, 2013 at 9:10 am
dave harris 45446 (1/9/2013)
Hi allCan anyone advise please, I have a TSQL 2008r2 headache...
I'm calculating the geo distance between a previous geo point and current geo point.
The code below, as a SELECT statement, returns 1.1 M rows and seems to be good.
When run as an UPDATE it sets all 1.8 M rows and with a zero value :ermm:
WITH tblCTE AS -- create common table expression
(SELECT
Row_Number() OVER (ORDER BY varVehicleReg, dteEvent) AS RowNumber
,dteEvent, varVehicleReg, geoPoint, fltDistanceMoved
FROM stageCoAvlsLog WHERE intFix > 0 -- all geoPoint are populated.
)
SELECT Cur.dteEvent, Cur.varVehicleReg, Cur.fltDistanceMoved,
-- or ...
--UPDATE stageCoAvlsLog SET fltDistanceMoved =
Cur.geoPoint.STDistance(Prv.geoPoint)-- AS fltMetres
FROM tblCTE AS Cur
LEFT OUTER JOIN tblCTE AS Prv
ON Cur.RowNumber = Prv.RowNumber + 1 -- join on current and previous row
AND Cur.varVehicleReg=Prv.varVehicleReg
-- the table defs are
[dteEvent] [datetime] NOT NULL,
[varVehicleReg] [varchar](32) NOT NULL,
[geoPoint] [geography] NULL,
[fltDistanceMoved] [real] NULL,
The http://msdn.microsoft.com/en-us/library/ms177523(v=sql.105).aspx says "Only the rows returned by the common table expression are modified." Hmm... 1 issue or 2?
Dave
--edit--
Misread the issue...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 9, 2013 at 9:17 am
dave harris 45446 (1/9/2013)
Hi allCan anyone advise please, I have a TSQL 2008r2 headache...
I'm calculating the geo distance between a previous geo point and current geo point.
The code below, as a SELECT statement, returns 1.1 M rows and seems to be good.
When run as an UPDATE it sets all 1.8 M rows and with a zero value :ermm:
WITH tblCTE AS -- create common table expression
(SELECT
Row_Number() OVER (ORDER BY varVehicleReg, dteEvent) AS RowNumber
,dteEvent, varVehicleReg, geoPoint, fltDistanceMoved
FROM stageCoAvlsLog WHERE intFix > 0 -- all geoPoint are populated.
)
SELECT Cur.dteEvent, Cur.varVehicleReg, Cur.fltDistanceMoved,
-- or ...
--UPDATE stageCoAvlsLog SET fltDistanceMoved =
Cur.geoPoint.STDistance(Prv.geoPoint)-- AS fltMetres
FROM tblCTE AS Cur
LEFT OUTER JOIN tblCTE AS Prv
ON Cur.RowNumber = Prv.RowNumber + 1 -- join on current and previous row
AND Cur.varVehicleReg=Prv.varVehicleReg
-- the table defs are
[dteEvent] [datetime] NOT NULL,
[varVehicleReg] [varchar](32) NOT NULL,
[geoPoint] [geography] NULL,
[fltDistanceMoved] [real] NULL,
The http://msdn.microsoft.com/en-us/library/ms177523(v=sql.105).aspx says "Only the rows returned by the common table expression are modified." Hmm... 1 issue or 2?
Dave
As far as that last comment goes, the code you're referring to does an INNER JOIN on the CTE. You're doing a LEFT JOIN, which means that not only the rows that match will get updated, but also the rows that DON'T match. Change LEFT JOIN to INNER JOIN if you want only the rows specified in the CTE to be updated.
Roland Alexander
The Monday Morning DBA
There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
January 9, 2013 at 11:49 am
The problem is that the table you are updating is not referenced (directly) in your FROM clause, so it is updating every row in that table. You want to change the updated table to one of the CTE aliases.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 10, 2013 at 2:44 am
The UPDATE of the current CTE works. So simple, yet hard to see.
Many thanks.
Dave
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply