I am attempting to add the result of my query into the column of an existing table. Thus far the query below finds the nearest electric substation within 30 kms of an electric line.
Select el1.id,
el1.geog4269.STAsText(),
(Select TOP 1 es.name from Test.dbo.electric_substations as es
with (index(sidx_es))
WHERE el1.geog4269.STDistance(es.geog) < 30000
order by el1.geog4269.STDistance(es.geog)
)As NearestElectricSubstation
from Test.dbo.electric_lines AS el1;
Now what I want to do is update a table called NNElines which has the following schema:
CREATE TABLE NNElines
(
id INT NOT NULL PRIMARY KEY,
Location geography NOT NULL,
Nearest_Esub varchar(50) NOT NULL
);
I want to update id with el1.id from the results, location with el1.geog4269.STAsText(), and Nearest_Esub with NearestElectricSubstation. I am trying a Update query but not getting anything. Any help is appreciated. Thank you
Update Test.dbo.NNElines
SET id = el1.id,
Location = el1.geog4269.STAsText()
From(
Select
fnc.el1.id,
fnc. el1.geog4269.STAsText()
From Test.dbo.electric_lines AS el1
CROSS APPLY
(Select TOP 1 es.name from Test.dbo.electric_substations as es
with (index(sidx_es))
WHERE el1.geog4269.STDistance(es.geog) < 30000
order by el1.geog4269.STDistance(es.geog)
) fnc
--As NearestElectricSubstation
--from Test.dbo.electric_lines AS el1;
);
Try this. You can use JOINs when updating tables. So I joined your query to the NNElines table on ID and updates the NNElines table with the corresponding values from your query.
UPDATE NNElines
SET a.location = b.geog4269.STAsText(),
a.Nearest_Esub = b.NearestElectricSubstation
FROM NNElines a
JOIN
(Select el1.id,
el1.geog4269.STAsText(),
(Select TOP 1 es.name from Test.dbo.electric_substations as es with (index(sidx_es))
WHERE el1.geog4269.STDistance(es.geog) < 30000
order by el1.geog4269.STDistance(es.geog)
)As NearestElectricSubstation
from Test.dbo.electric_lines AS el1) b
ON a.id = b.id
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments