current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm trying to do something like the following:

UPDATE table1
SET table1.nearest_city_id = subquery.id
FROM 
  (SELECT id FROM cities ORDER BY cities.location <-> table1.location LIMIT 1)
  AS subquery;

i.e. set the nearest city in table 1 based on a spatial query..

But I can't reference the row I'm updating within the subquery. Any way around this?

share|improve this question
    
@a_horse_with_no_name Yes i agree: Deleted. –  Houari Feb 14 '14 at 7:50

2 Answers 2

up vote 1 down vote

Something like this:

UPDATE table1
  SET nearest_city_id = (select id
                         from cities c
                         ORDER BY c.location <-> table1.location
                         LIMIT 1);
share|improve this answer
up vote 1 down vote

If you need update 1 column of table1 by 1 column of table2 by one condition : 

UPDATE Table1 
 SET Table1.Column = A.Column 
      FROM(SELECT Column,Id FROM Table2) As A
 WHERE Table1.Id=Table2.Id
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.