PostgreSQL – Update Table with Data from Another Table

greatest-n-per-grouppostgispostgresqlupdate

I want to update the table car_check field fencing.(total 40 rows).I also use postgis function.
I think it's a little difficult.Any suggestions?

The code is the first carid example.

UPDATE car_check 
set fencing=ST_Contains
from (
SELECT ST_Contains(ST_AsText('01030000..'),
'POINT(23.912784 120.99178)') 
)as foo
where carid='AD-5487' and cargroupid='1'

enter image description here

Best Answer

Try this untangled version to update all rows of char_check:

UPDATE car_check c
SET    fencing = ST_Contains(u.geom, ST_MakePoint(t.lat::float8, t.lng::float8))
FROM   test t
      ,rule r
JOIN   (
   SELECT DISTINCT ON (polyname)
          polyname, geom
   FROM   unknown_table
   ORDER  BY polyname, version DESC
    ) u USING (polyname)
WHERE  t.id = c.carid
AND    r.cargroupid = c.cargroupid

Major points

Use the simpler ST_MakePoint to construct a point geometry from numeric input.
You do not need ST_AsText() at all, since your column geom is of type geometry already.
Fastest and simplest way to get the geometry for the biggest version per polyname in table unknown_table is to use DISTINCT ON, which is a Postgres extension of the SQL standard. More details in this related answer:
How do I efficiently get "the most recent corresponding row"?
In reference to the currently accepted but invalid answer: the concatenation operator in Postgres (and standard SQL) is || (not +)

Related Solutions

MySQL update one table from another joined many-to-many

This works - I had the JOIN wrong

UPDATE `events` 
  INNER JOIN `plays_in_events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE 
  events.Name IS NULL and plays_in_events.PlayID = 1

and the earlier comments were quite right, but it took me a while to 'get it' - thanks for your help. I had just copied the JOIN lines from the working SELECT query, but when I looked again, I could finally see what the commentators meant about events appearing twice, and plays_in_events not appearing properly in the JOINs.

ST_Contains Aggregation Problem

It's difficult without the data but let's recreate a mental experiment.

CREATE TEMPORARY TABLE foo AS
SELECT * FROM ( VALUES
 ( 1, 2, true ),
 ( 1, 3, true ),
 ( 1, 4, false)
) AS t(cp, bd, contains);

Now... What happens if I join this table to itself on condition that returns true more than once.

SELECT *
FROM foo AS f1
JOIN foo AS f2
  USING (contains);
 contains | cp | bd | cp | bd 
----------+----+----+----+----
 t        |  1 |  2 |  1 |  3
 t        |  1 |  2 |  1 |  2
 t        |  1 |  3 |  1 |  3
 t        |  1 |  3 |  1 |  2
 f        |  1 |  4 |  1 |  4
(5 rows)

Now this may make perfect sense in this form, but if you conceal a table from the result..

SELECT f1.*
FROM foo AS f1
JOIN foo AS f2
  USING (contains);
 cp | bd | contains 
----+----+----------
  1 |  2 | t
  1 |  2 | t
  1 |  3 | t
  1 |  3 | t
  1 |  4 | f

Now you're going to be confused. So the problem here is that you have multiple cases where one cp.geom contains numerous bd.geoms

Now how does that work if you add a GROUP BY..

SELECT f1.*, sum (f2.bd)
FROM foo AS f1
JOIN foo AS f2
  USING (contains)
GROUP BY f1.cp, f1.bd, f1.contains
 cp | bd | contains | sum 
----+----+----------+-----
  1 |  3 | t        |   5
  1 |  2 | t        |   5
  1 |  4 | f        |   4

And, that's precisely your problem. Here you're getting a sum() of 5. Which is understood given the above tables that produced that.

So if you want to debug this check the condition ST_Contains(cp.geom, bd.geom) output cp.geom and bd.geom using ST_AsText()

Best Answer

Major points

Related Solutions

MySQL update one table from another joined many-to-many

ST_Contains Aggregation Problem

Related Question