Update existing table with query results SQL Server 2012

Question

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;
    );

You need to use [`INSERT` with `SELECT`](http://technet.microsoft.com/en-us/library/ms188263(v=sql.105).aspx). — PM 77-1, Jul 21 '14 at 23:38
This is confusing. Are you trying to `UPDATE` existing rows that exist in NNElines or `INSERT` new rows? — SQLChao, Jul 21 '14 at 23:39
The table already has the rows created, so I am only trying to update the table.Hope it is clear now, I am just starting to use this, so sorry for any confusion — elseflyer, Jul 21 '14 at 23:46
@Jonast92 I have updated what I have tried so far. Thank you — elseflyer, Jul 21 '14 at 23:54
Update statements work like this: UPDATE colummn SET field = value WHERE condition. Your values in this case are the select statement, preferably configurated with a join but not necessarily. — Jonast92, Jul 21 '14 at 23:57

score 0 · Accepted Answer · answered Jul 22 '14 at 00:34

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

Update existing table with query results SQL Server 2012

1 Answers1