Update multiple rows in a table from another table

问题:

I have two tables.

Table1 contains companies whose locations are georeferenced with lat/lng coordinates in a column called the_geom

Table2 also contain the same companies from Table1, not georeferenced, along with hundreds of other companies whose addresses are georeferenced.

All I need to do is insert the_geom lat/lng values from Table1 companies into their corresponding entries in Table 2. The common denominator on which these inserts can be based on is the address column.

Simple question, I am sure, but I rarely use SQL.

回答1:

Assuming that by

insert "the_geom" lat/lng values

you actually mean to update existing rows in table2:

UPDATE table2 t2
SET    the_geom = t1.the_geom
FROM   table1 t1
WHERE  t2.address = t1.address
AND    t2.the_geom IS DISTINCT FROM t1.the_geom; -- avoid empty updates

Also assuming that the address column has UNIQUE values.
Details about UPDATE in the excellent manual here.

回答2:

If you are a mysql user(like me) and if the above script is not working, here is the mysql equivalent.

UPDATE table2 t2, table1 t1
SET    the_geom = t1.the_geom
WHERE  t2.address = t1.address
AND    t2.the_geom <> t1.the_geom; -- avoid empty updates

All credits to the OP.

回答3:

I had a similar problem, but when I tried the solutions mentioned above, I got an error like

Incorrect syntax near 't2'

The code that worked for me is:

UPDATE table2
SET the_geom = t1.the_geom
FROM table1 as t1
WHERE table2.address = t1.address AND table2.the_geom <> t1.the_geom

I know that my answer is 5 years late, but I hope this will help someone like me, who couldn't find this solution.