Обсуждение: UPDATE using subquery with joined tables

Hi,

I need to update field1 in table1, gathering data from field1 in table2.
The following SELECT shows the data as it needs to be updated:

SELECT a.field1 || regexp_replace(b.field1, '.*(mypattern)', e'. \\1')
FROM table1 a JOIN table2 b USING (id)
WHERE a.field1 NOT LIKE '%mypattern%' AND b.field1 LIKE '%mypattern%';

I am not sure how to translate this into an UPDATE statement for fiel1
in table1 efficiently.  Any tips welcome.

Cheers,

--
Seb

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Sebastian P. Luque
> Sent: Tuesday, December 04, 2012 2:53 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] UPDATE using subquery with joined tables
>
> Hi,
>
> I need to update field1 in table1, gathering data from field1 in table2.
> The following SELECT shows the data as it needs to be updated:
>
> SELECT a.field1 || regexp_replace(b.field1, '.*(mypattern)', e'. \\1')
FROM
> table1 a JOIN table2 b USING (id) WHERE a.field1 NOT LIKE '%mypattern%'
> AND b.field1 LIKE '%mypattern%';
>
> I am not sure how to translate this into an UPDATE statement for fiel1 in
> table1 efficiently.  Any tips welcome.
>
> Cheers,
>
> --
> Seb

Ignoring the "efficiently" part the general form for a joining update is:

UPDATE table SET field = src.field
FROM (

SELECT id, field FROM ... -- make this query as complex as needed; including
WITH if necessary

) src
WHERE table.id = src.id;

SO <Not Tested>:

UPDATE table1 SET field1 = t2.new_field_1
FROM (

SELECT id                              --# need to add the linking ID to the
subquery
, a.field1 || regexp_replace(b.field1, '.*(mypattern)', e'. \\1') AS
new_field_1  --# provide an alias for this column
FROM table1 a JOIN table2 b USING (id) WHERE a.field1 NOT LIKE '%mypattern%'
AND b.field1 LIKE '%mypattern%';

) t2
WHERE table1.id = t2.id;

David J.

Seems like this should work (untested though):

UPDATE table1 a
SET field1 = a.field1 || (SELECT regexp_replace(b.field1, '....', '...') FROM table2 b WHERE a.id = b.id AND b.field1 LIKE '....')
WHERE a.field1 NOT LIKE '....'

Paul