> -----Original Message----- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@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
