UPDATE table with another table data

Question

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'

Answer 1

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 +)

Answer 2

You don't seem to join the tables you are trying to update from. I think the query will need to be converted to this:

UPDATE car_check 
set fencing=foo.ST_Contains
from (
SELECT ST_Contains(ST_AsText(p.geom),
ST_GeomFromText('POINT(' || to_char(t.lat, '9999.999999') || ' ' || to_char(t.log, '9999.999999') || ')') AS ST_Contains, cc.carid, cc.cargroupid
FROM test t INNER JOIN car_check cc ON t.id = cc.carid
INNER JOIN rule r ON cc.cargroupid = r.cargroupid
INNER JOIN poly p ON r.polyname = p.polyname
WHERE p.poly_version = (SELECT MAX(p1.poly_version) FROM poly p1 WHERE p1.polyname = p.polyname))as foo 
INNER JOIN car_check 
ON foo.carid = car_check.carid AND foo.cargroupid = car_check.cargroupid
where carid='AD-5487' and cargroupid='1'

(I have no idea how your last table is called. The name is cut off from the picture. I called it poly in my statement. Fix it in yours)