UPDATE using subquery with joined tables

-----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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

On Tue, Dec 4, 2012 at 11:52 AM, Sebastian P. Luque <spluque@gmail.com>wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
_________________________________
Pulchritudo splendor veritatis.