Re: Temp tables

[Igor Andriychuk <2.andriychuk@xxxxxxxxx>]

I said stupid thing :-) you can’t use with with update only with select, I forgot you are about update.

On Aug 14, 2020, at 5:09 PM, jose fuenmayor <jafn82@xxxxxxxxx> wrote:

---------- Forwarded message ---------
De: jose fuenmayor <jafn82@xxxxxxxxx>
Date: vie., 14 de agosto de 2020 20:07
Subject: Re: Temp tables
To: 2.andriychuk <2.andriychuk@xxxxxxxxx>, <psql-admin@xxxxxxxxxxxxxx>

Could you show me the modified query using with and join??

El vie., 14 de agosto de 2020 20:01, 2.andriychuk <2.andriychuk@xxxxxxxxx> escribió:

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@xxxxxxxxx>

Date: 8/14/20 16:27 (GMT-08:00)

To: pgsql-admin@xxxxxxxxxxxxxx

Subject: Temp tables

Hi all , i have a doubt wich query performs better

Update 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