Is it possible to build an update statement with a subselect? I mean, I have a table T1 with two fields: Field1 (PRIMARY KEY), Field2. I have another table T2 with two fields: T2_1, T2_2. I need something like this:
UPDATE T1 SET Field2 = (SELECT MIN(T2.T2_2) FROM T2 WHERE T2.T2_1 = T1.Field1)
I can't solve the problem this way:
UPDATE T1 FROM T2 SET Field2 = T2_2 WHERE Field1 = T2_1
because there are multiple rows in T2 for each value of Field1.
update a from table1 a , ( select column1 ,min(column2) as column2 from table2 group by 1 ) b set column2 = b.column2 where a.column1 = b.column1 ;
If table2 has more rows than table1 you might want to include a join in the derived table to improve performance.
update a from table1 a , ( select b1.column1 ,min(b1.column2) as column2 from table1 a1 inner join table2 b1 on a1.column1 = b1.column1 group by 1 ) b set column2 = b.column2 where a.column1 = b.column1 ;