pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hans-Jürgen Schönig <hs@cybertec.at>
To: pgsql-sql@postgresql.org
Subject: Re: multiple sql update w/ major time issues
Date: Thu, 07 Jun 2001 10:26:42 +0200
Message-ID: <3B1F3AC2.7E9A330D@cybertec.at> (raw)
References: <aaac9c0b.0106060723.57cfa53a@posting.google.com>

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





view thread (3+ messages)  latest in thread

Message-ID: <3B1F3AC2.7E9A330D@cybertec.at>
Permalink:  ../3B1F3AC2.7E9A330D@cybertec.at/
Also on:    postgresql.org/message-id/3B1F3AC2.7E9A330D@cybertec.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: hs@cybertec.at
  Subject: Re: multiple sql update w/ major time issues
  In-Reply-To: <3B1F3AC2.7E9A330D@cybertec.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox