Hi Jose,
If table2 is really big and you run this update on regular base just make sure you have a covering index on pair x and field2 with field1 in include option, then put both x=z and a.field2=b.field2 to where clause.
You don't have to use subquery, just table straight forward, index will do the trick for you.
Create index <index name> on table2(x, field2) include(field1);
But include is available starting from v11.
If version of your PostgreSQL < 11, use with to express your subquery, then use it in join.
Best,
Igor
Sent from my Verizon, Samsung Galaxy smartphone
-------- Original message --------
From: jose fuenmayor <jafn82@gmail.com>
Date: 8/14/20 16:27 (GMT-08:00)
To: pgsql-admin@postgresql.org
Subject: Temp tables
Hi all , i have a doubt wich query performs betterUpdate table a set field1=b.field 1
From (select field1,field2
From table2 where x=z
) b
Where a.field2=b.field2
Or
Create temp table y as
select field1,field2
From table2 where x=z;
Update table a
set field1=b.field1
From y as b
Where a.field2=b.field2
I ve been told not to use subquerys, but i think this refers to
Select * from table where field1 in (select field1 from table2)
Any thoughts?
Thanks people y'all
