当条件存在时,从另一个表更新表中的多个行

时间:2023-01-17 23:08:27

I have two tables.

我有两张桌子。

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

表1包含在名为the_geom的列中使用lat / lng坐标进行地理配准的公司

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

表2还包含来自Table1的相同公司,而不是地理参考,以及其他数百个地址为地理参考的公司。

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.

我需要做的就是将Table1公司的the_geom lat / lng值插入表2中的相应条目。这些插入可以基于的公分母是地址列。

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

简单的问题,我确定,但我很少使用SQL。

3 个解决方案

#1


20  

Assuming that by

假设通过

insert "the_geom" lat/lng values

插入“the_geom”lat / lng值

you actually mean to update existing rows in table2:

你实际上是要更新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.

还假设地址列具有UNIQUE值。有关UPDATE的详细信息,请参见优秀手册。

#2


3  

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

如果你是一个mysql用户(像我一样),如果上面的脚本不起作用,这里是mysql等价物。

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.

OP的所有学分。

#3


2  

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

我有类似的问题,但是当我尝试上面提到的解决方案时,我得到了一个错误

Incorrect syntax near 't2'

'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.

我知道我的答案迟了5年,但我希望这会帮助像我这样找不到这个解决方案的人。

#1


20  

Assuming that by

假设通过

insert "the_geom" lat/lng values

插入“the_geom”lat / lng值

you actually mean to update existing rows in table2:

你实际上是要更新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.

还假设地址列具有UNIQUE值。有关UPDATE的详细信息,请参见优秀手册。

#2


3  

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

如果你是一个mysql用户(像我一样),如果上面的脚本不起作用,这里是mysql等价物。

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.

OP的所有学分。

#3


2  

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

我有类似的问题,但是当我尝试上面提到的解决方案时,我得到了一个错误

Incorrect syntax near 't2'

'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.

我知道我的答案迟了5年,但我希望这会帮助像我这样找不到这个解决方案的人。