From: | Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: UPDATE using subquery with joined tables |
Date: | 2012-12-04 20:07:02 |
Message-ID: | CA+6hpam4CtnCH5FxFueE5obOrQ7VUwociinwETsC+3uaFzBfEA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
_________________________________
Pulchritudo splendor veritatis.
From | Date | Subject | |
---|---|---|---|
Next Message | Sergey Konoplev | 2012-12-04 20:49:15 | Re: Which is faster: char(14) or varchar(14) |
Previous Message | David Johnston | 2012-12-04 20:04:17 | Re: UPDATE using subquery with joined tables |