Thread: Update from select
PG 8.4 Having trouble putting together an update query to update multiple columns in tbl1 from columns in tbl2. update tbl1 set col3,col4,col5 from (select col3, col4,col5 from tbl2 where col1="criteria") Can someone add to the Postgres Docs (shown below) to help me with this. UPDATE employees SET sales_count = sales_count + 1 WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); Many thanks Bret Stern
> PG 8.4
>
> Having trouble putting together an update query to update multiple
> columns in tbl1 from columns in tbl2.
>
> update tbl1
> set col3,col4,col5
> from
> (select col3, col4,col5 from tbl2 where col1="criteria")
>
>
>
> Can someone add to the Postgres Docs (shown below) to help me with
> this.
>
> UPDATE employees SET sales_count = sales_count + 1 WHERE id =
> (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');
>
>
> Many thanks
>
> Bret Stern
Your question isn't very clear.
Are you updating all records in tbl1?
Or col1 in tbl1 should also match col1 in tbl2?
If that's the case:
UPDATE tbl1 t1
SET col3 = t2.col3, col4 = t2.col4, col5 = t2.col5
FROM tbl2 t2
WHERE t1.col1 = t2.col1
AND t.col1 = "criteria";
Igor Neyman
em stands for "easy money" update tbl1 set col3=em.col3,col4=em.col4,col5=em.col5 from (select col3, col4,col5 from tbl2 where col1="criteria") em Regards, Justin Tocci Programmer www.workflowproducts.com 7813 Harwood Road North Richland Hills, TX 76180 phone 817-503-9545 skype justintocci On May 13, 2013, at 3:23 PM, Bret Stern <bret_stern@machinemanagement.com> wrote: > PG 8.4 > > Having trouble putting together an update query to update > multiple columns in tbl1 from columns in tbl2. > > update tbl1 > set col3,col4,col5 > from > (select col3, col4,col5 from tbl2 where col1="criteria") > > > > Can someone add to the Postgres Docs (shown below) to help me with this. > > UPDATE employees SET sales_count = sales_count + 1 WHERE id = > (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); > > > Many thanks > > Bret Stern > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Le lundi 13 mai 2013 à 13:23 -0700, Bret Stern a écrit : > PG 8.4 > > Having trouble putting together an update query to update > multiple columns in tbl1 from columns in tbl2. > > update tbl1 > set col3,col4,col5 > from > (select col3, col4,col5 from tbl2 where col1="criteria") > UPDATE tbl1 SET col3=t2.col3, col4=t2.col4, col5=t2.col5 FROM tbl2 t2 WHERE t2.col1="criteria" -- Salutations, Vincent Veyron http://gdlc.fr/logiciels Applications de gestion des sinistres assurance et des contentieux juridiques
Easy money indeed. Thanks for the help On Mon, 2013-05-13 at 15:28 -0500, Justin Tocci wrote: > em stands for "easy money" > > update tbl1 > set col3=em.col3,col4=em.col4,col5=em.col5 > from > (select col3, col4,col5 from tbl2 where col1="criteria") em > > Regards, > > Justin Tocci > Programmer > www.workflowproducts.com > 7813 Harwood Road > North Richland Hills, TX 76180 > phone 817-503-9545 > skype justintocci > > On May 13, 2013, at 3:23 PM, Bret Stern <bret_stern@machinemanagement.com> wrote: > > > PG 8.4 > > > > Having trouble putting together an update query to update > > multiple columns in tbl1 from columns in tbl2. > > > > update tbl1 > > set col3,col4,col5 > > from > > (select col3, col4,col5 from tbl2 where col1="criteria") > > > > > > > > Can someone add to the Postgres Docs (shown below) to help me with this. > > > > UPDATE employees SET sales_count = sales_count + 1 WHERE id = > > (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); > > > > > > Many thanks > > > > Bret Stern > > > > > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > >