Update multiple rows in a table from another table when condition exists

Question

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.

you dont insert rows here, you update. – John Woo Oct 13 '12 at 03:15 — John Woo, Oct 13 '12 at 03:15

Erwin Brandstetter · Accepted Answer · 2021-08-11T12:30:11.790

31

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

Related answer:

How do I (or can I) SELECT DISTINCT on multiple columns?

Also assuming that the address column has UNIQUE values.
Details for UPDATE in the manual.

edited Aug 11 '21 at 12:30

answered Oct 13 '12 at 03:09

Erwin Brandstetter

605,456
145
1,078
1,228

1

Yeap, exactly what I meant, this did the trick. Thanks Erwin. – John Oct 13 '12 at 03:18
@LP13: Yes. If you follow the link to the manual I provided, you see `UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]` in the "Synopsis" right at the top. (Syntax for some other RDBMS differs, see "Compatibility" at the bottom of the same page.) – Erwin Brandstetter Sep 18 '17 at 23:05
UPDATE table2 SET the_geom = t1.the_geom FROM table1 t1, table2 t2 WHERE t2.address = t1.address AND t2.the_geom IS DISTINCT FROM t1.the_geom; – AMRESH PANDEY Apr 07 '18 at 07:26
@AMRESHPANDEY: The additional join to `table2` is redundant. (Or what are you trying to tell us?) – Erwin Brandstetter Apr 07 '18 at 12:54
@ErwinBrandstetter How shall I do this in Oracle? – coderzzz18 Aug 11 '21 at 06:44
1

@coderzzz18: Oracle has no `FROM` clause for `UPDATE` . See: https://www.postgresql.org/docs/current/sql-update.html#id-1.9.3.182.10 There are a couple of alternatives (not as straight forward IMO) See: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10008.htm#i2067715 and https://stackoverflow.com/q/7030699/939860 – Erwin Brandstetter Aug 11 '21 at 12:27
Thanks @ErwinBrandstetter , I achieved through Merge command and when matched I updated. I hope that helps to others like me :) – coderzzz18 Aug 12 '21 at 06:39

score 11 · Answer 2 · answered May 17 '18 at 13:29

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.

score 6 · Answer 3 · answered May 10 '13 at 09:18

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.

Update multiple rows in a table from another table when condition exists

3 Answers3

Linked