CODEWITHSUNDEEP
sqlpostgresqlsql-updatepostgis
John
John

Reputation: 822

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

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.

Upvotes: 18

Views: 56388

Answers (4)

S Cooley
S Cooley

Reputation: 1

-- e.g. scenario for demo
--Creating a rollup monthly customer invoice 
--For customers that buy individual items on credit.

--What I am demonstrating here is that you can update 
--a table based on some joins etc.. from a complex query

WITH cte as (
    SELECT 
         c.customerId  --get cusomers
        ,sum(o.Cost) as cost --sum all the individual orders 
    FROM customers c
    LEFT JOIN orders o on c.orderId = o.orderId
    where 1=1
    AND orderId in (
         115254
        ,115270
        ,115285
        ,115291
        ,115319
        ,115324
        ,115325
    )
    group by orderId
)

--updating based on the sum of all orders by customer
UPDATE invoice 
SET Customer_Invoice_Amount = cte.cost
FROM  cte
WHERE invoice.orderId = cte.orderId

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

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:

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

Upvotes: 33

levkaster
levkaster

Reputation: 2730

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.

Upvotes: 11

Jay
Jay

Reputation: 720

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.

Upvotes: 6

Related Questions