[postgis-users] update with Inner join

[Robert Burgholzer]

In cases where I have many inner joins, I generally create an alieased
sub-query to serve as my update data source. Following the previous
posters convention:

UPDATE test1 SET value = a.newval 
FROM ( select t1.idcol, t1.value as newval 
       from test1 as t1 INNER JOIN test2 ON t1.extid=test2.extid 
       inner join t3 on t2.othercolumn = t3.somecolumn ) as a
where test1.idcol = a.idcol;


-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
svidal at proditecdis.com
Sent: Tuesday, January 10, 2006 2:42 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] update with Inner join

Hi
I have a problem with the update.

I have:

update temp_corr_xxx  set existe = 5
	from  temp_corr_xxx tm
	inner join geom_fincas gf on
		tm.cod_finca = gf.cod_finca and tm.tabla = 12;

and all the table update.

Well, i see in this "list", that

> UPDATE test1 SET value = 10 FROM test1 t1 INNER JOIN test2 ON
t1.extid=test2.extid WHERE t1.id=1;

> This expression update all record i table "test1".

Yup, that's what it should do.  "FROM test1 t1" adds an additional table
reference that's unconnected to the update target table.  You should
have written

UPDATE test1 SET value = 10
FROM test2 WHERE test1.extid=test2.extid AND test1.id=1;

well, but
if i have many inner join?
it's no possible use "table alias" also?

Thank's
Salvador Vidal

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users