Обсуждение: Is there a batch/bulk UPDATE syntax available?

Hi,

Is there a batch/bulk UPDATE query syntax available in PostgreSQL, similar
to multirow VALUES syntax available for INSERT?

INSERT Multirow VALUES syntax example:
INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

There is a situation in my application, where I am performing lots and lots
of updates on individual rows. I am trying to figure out how to make the
updates faster.

Any other ideas/ways to make updates faster are highly appreciated.

Regards,
Gnanam

Gnanakumar, 03.02.2011 13:00:
> Is there a batch/bulk UPDATE query syntax available in PostgreSQL, similar
> to multirow VALUES syntax available for INSERT?
>
> INSERT Multirow VALUES syntax example:
> INSERT INTO films (code, title, did, date_prod, kind) VALUES
>      ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
>      ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
>
> There is a situation in my application, where I am performing lots and lots
> of updates on individual rows. I am trying to figure out how to make the
> updates faster.
>
> Any other ideas/ways to make updates faster are highly appreciated.
>
> Regards,
> Gnanam
>
>
Maybe this pattern would work for you:

UPDATE my_table
    SET the_column = CASE
                       WHEN some_id_column = 1 then 100
                       WHEN some_id_column = 2 then 200
                       WHEN some_id_column = 3 then 300
                       WHEN some_id_column = 4 then 400
                       WHEN some_id_column = 5 then 500
                     END
WHERE some_id_column IN (1,2,3,4,5)

But that get's pretty nasty if you have more than just those 5 rows.

Show us explain analyze for this update.

2011/2/3, Gnanakumar <gnanam@zoniac.com>:
> Hi,
>
> Is there a batch/bulk UPDATE query syntax available in PostgreSQL, similar
> to multirow VALUES syntax available for INSERT?
>
> INSERT Multirow VALUES syntax example:
> INSERT INTO films (code, title, did, date_prod, kind) VALUES
>     ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
>     ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
>
> There is a situation in my application, where I am performing lots and lots
> of updates on individual rows. I am trying to figure out how to make the
> updates faster.
>
> Any other ideas/ways to make updates faster are highly appreciated.
>
> Regards,
> Gnanam
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>


--
------------
pasman

Here is my UPDATE query and EXPLAIN ANALYZE output:

UPDATE query:
UPDATE MYUPDATETABLE SET ABOOLEANCOLUMN = TRUE WHERE COMPANYID = 999 AND
EMAIL1 = 'someemail@somedomain.com'

EXPLAIN ANALYZE output:
Bitmap Heap Scan on myupdatetable  (cost=190.89..16107.70 rows=45
width=1826) (actual time=31.150..31.155 rows=2 loops=1)
  Recheck Cond: (companyid = 999::numeric)
  Filter: ((email1)::text = 'someemail@somedomain.com'::text)
  ->  Bitmap Index Scan on mut_cmpid_indx  (cost=0.00..190.88 rows=9051
width=0) (actual time=13.868..13.868 rows=60206 loops=1)
        Index Cond: (companyid = 999::numeric)
Total runtime: 31.319 ms

-----Original Message-----
From: pasman pasmanski [mailto:pasman.p@gmail.com]
Sent: Friday, February 04, 2011 2:01 AM
To: gnanam@zoniac.com
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Is there a batch/bulk UPDATE syntax available?

Show us explain analyze for this update.

2011/2/3, Gnanakumar <gnanam@zoniac.com>:
> Hi,
>
> Is there a batch/bulk UPDATE query syntax available in PostgreSQL, similar
> to multirow VALUES syntax available for INSERT?
>
> INSERT Multirow VALUES syntax example:
> INSERT INTO films (code, title, did, date_prod, kind) VALUES
>     ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
>     ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
>
> There is a situation in my application, where I am performing lots and
lots
> of updates on individual rows. I am trying to figure out how to make the
> updates faster.
>
> Any other ideas/ways to make updates faster are highly appreciated.
>
> Regards,
> Gnanam
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>


--
------------
pasman

create table tmp
(
  id serial not null primary key,
  name text,
  age integer
);

insert into tmp (name,age) values ('keith', 43),('leslie', 40),('bexley', 19),('casey', 6);

update tmp
 set age = data_table.age
from
(
  select 'keith'::text as name, 44::integer as age
 union
  select 'leslie', 50
 union
  select 'bexley', 10
 union
  select 'casey', 12
) as data_table
where
 tmp.name = data_table.name;

On Thu, Feb 3, 2011 at 7:00 AM, Gnanakumar <gnanam@zoniac.com> wrote:
> Hi,
>
> Is there a batch/bulk UPDATE query syntax available in PostgreSQL, similar
> to multirow VALUES syntax available for INSERT?
>
> INSERT Multirow VALUES syntax example:
> INSERT INTO films (code, title, did, date_prod, kind) VALUES
>    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
>    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
>
> There is a situation in my application, where I am performing lots and lots
> of updates on individual rows. I am trying to figure out how to make the
> updates faster.
>
> Any other ideas/ways to make updates faster are highly appreciated.
>
> Regards,
> Gnanam
>
>