条件存在时从另一个表更新一个表中的多行 [英] Update multiple rows in a table from another table when condition exists

问题描述

我有两个桌子.

表1 包含在the_geom

列中使用纬度/经度坐标进行地理定位的公司

表2 还包含来自表1 的相同公司(未进行地理定位)，以及其他数百家地址已进行地理定位的公司.

我要做的就是将表1 公司的the_geom纬度/经度值插入到表2 中的相应条目中.这些插入物所基于的共同点是address列.

我敢肯定，这是一个简单的问题，但是我很少使用SQL.

解决方案

假设通过

插入"the_geom"纬度/经度值

您实际上的意思是更新 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

还要假设address列具有UNIQUE值.
优秀手册此处中有关UPDATE的详细信息.. >

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.

解决方案

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.

这篇关于条件存在时从另一个表更新一个表中的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！