Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5CGXhE41440 for ; Tue, 12 Jun 2001 12:33:43 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f578SeE90096 for ; Thu, 7 Jun 2001 04:28:40 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.1/8.11.1) id f578SUM21465 for pgsql-sql@postgresql.org; Thu, 7 Jun 2001 04:28:30 -0400 (EDT) (envelope-from news) From: =?iso-8859-1?Q?Hans=2DJ=FCrgen=20Sch=F6nig?= X-Newsgroups: comp.databases.postgresql.sql Subject: Re: multiple sql update w/ major time issues Date: Thu, 07 Jun 2001 10:26:42 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 66 Message-ID: <3B1F3AC2.7E9A330D@cybertec.at> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Mailer: Mozilla 4.77 [de] (X11; U; Linux 2.4.2-2 i686) X-Accept-Language: en To: pgsql-sql@postgresql.org X-Archive-Number: 200106/184 X-Sequence-Number: 3424 Dawn schrieb: > I have a sql update statement that is dealing with hundreds of > thousands of records. It takes hours and hours to complete (if it > does complete and not take down the server). Here is how I have it > right now: > > update aud_member_ext_attributes b > set EXTVALUE217 = > (select a.MTD > from gl_totals a > where a.category = 'tankrent' > and a.CUST_NO = b.EXTVALUE101 > and a.DIST_NO = b.EXTVALUE102 > and a.SUB_NO = b.EXTVALUE105 > and a.FUEL_TYPE = b.EXTVALUE123); > > update aud_member_ext_attributes b > set EXTVALUE223 = > (select a.YTD > from gl_totals a > where a.category = 'tankrent' > and a.CUST_NO = b.EXTVALUE101 > and a.DIST_NO = b.EXTVALUE102 > and a.SUB_NO = b.EXTVALUE105 > and a.FUEL_TYPE = b.EXTVALUE123); > > update aud_member_ext_attributes b > set EXTVALUE229 = > (select a.R12 > from gl_totals a > where a.category = 'tankrent' > and a.CUST_NO = b.EXTVALUE101 > and a.DIST_NO = b.EXTVALUE102 > and a.SUB_NO = b.EXTVALUE105 > and a.FUEL_TYPE = b.EXTVALUE123); > > There are 3 "extvaluexxx" that are set for each "category" for a total > of 9 categories. This makes a grand total of 27 update statements. > Any suggestions? It would be much appreciated!!!!! Is there no way to do it in one statement? Try something like that: update aud_member_ext_attributes b set EXTVALUE223 = (select a.YTD from gl_totals a where a.category = 'tankrent' and a.CUST_NO = b.EXTVALUE101 and a.DIST_NO = b.EXTVALUE102 and a.SUB_NO = b.EXTVALUE105 and a.FUEL_TYPE = b.EXTVALUE123), EXTVALUE229 = (select a.R12 from gl_totals a where a.category = 'tankrent' and a.CUST_NO = b.EXTVALUE101 and a.DIST_NO = b.EXTVALUE102 and a.SUB_NO = b.EXTVALUE105 and a.FUEL_TYPE = b.EXTVALUE123); You can update multiple columns with just one update. Hans