Thread: Need help on updating an entire column with a list of values, I have.

Need help on updating an entire column with a list of values, I have.

From

"Rajan, Pavithra "

Date:

26 March 2010, 14:04:09

Hello - I have this table with 90 rows, which contains 2 columns ,column A (type 'numeric') and column B(type text) . Column 'A' is filled with a constant number and column 'B' has an unique entry for each row.

E.g.              A                 B
         (numeric)        (text)

            15968.0           002
        15968.0           003
        15968.0           004
        15968.0           011
        15968.0           012
        15968.0           057
        15968.0           006
        15968.0           009
        15968.0           010
             ..
             ..
I would here want to update the entire column A with a list of values that I have.( 06959.0,15308.0,15968.0,18916.2,19961.0,26528.0,29553.0,29872.0,30631.0 …).How do I accomplish this? Thank you.

Re: Need help on updating an entire column with a list of values, I have.

From

Szymon Guz

Date:

26 March 2010, 14:15:28

2010/3/26 Rajan, Pavithra <RAJANP@coned.com>

Hello - I have this table with 90 rows, which contains 2 columns ,column A (type 'numeric') and column B(type text) . Column 'A' is filled with a constant number and column 'B' has an unique entry for each row.
E.g.              A                 B
         (numeric)        (text)

            15968.0           002
        15968.0           003
        15968.0           004
        15968.0           011
        15968.0           012
        15968.0           057
        15968.0           006
        15968.0           009
        15968.0           010
             ..
             ..
I would here want to update the entire column A with a list of values that I have.( 06959.0,15308.0,15968.0,18916.2,19961.0,26528.0,29553.0,29872.0,30631.0 …).How do I accomplish this? Thank you.

Hi,

what is the relation between current data and the data that you have in the list? How do you want to update those values? Where should be the value 06959.0 and where 29872.0?

regards

Szymon Guz

Re: Need help on updating an entire column with a list of values, I have.

From

"Rajan, Pavithra "

Date:

26 March 2010, 14:18:33

Hello ,

Yes -I need to get the exact the same result as you had listed.Thanks.

From: Timo Klecker [mailto:klecker@decoit.de]
Sent: Friday, March 26, 2010 10:12 AM
To: Rajan, Pavithra ; pgsql-general@postgresql.org
Subject: AW: [GENERAL] Need help on updating an entire column with a list of values, I have.

Hello,

what do you expect as Result? Something like this?

E.g.              A                 B
         (numeric)        (text)

           06959.0           002
       15308.0           003
       15968.0           004
       18916.0           011
       19961.0           012
       26528.0           057
       29553.0           006
       29872.0           009
       30631.0           010

Mit freundlichen Grüßen

Timo Klecker

Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Rajan, Pavithra
Gesendet: Freitag, 26. März 2010 14:48
An: pgsql-general@postgresql.org
Betreff: [GENERAL] Need help on updating an entire column with a list of values, I have.

Re: Need help on updating an entire column with a list of values, I have.

From

"Timo Klecker"

Date:

26 March 2010, 14:18:41

Hello,

what do you expect as Result? Something like this?

Mit freundlichen Grüßen

Timo Klecker

Re: Need help on updating an entire column with a list of values, I have.

From

Thom Brown

Date:

26 March 2010, 14:24:37

On 26 March 2010 13:47, Rajan, Pavithra <RAJANP@coned.com> wrote:
> Hello - I have this table with 90 rows, which contains 2 columns ,column A
> (type 'numeric') and column B(type text) . Column 'A' is filled with a
> constant number and column 'B' has an unique entry for each row.
>
> E.g.              A                 B
>          (numeric)        (text)
>
>             15968.0           002
>         15968.0           003
>         15968.0           004
>         15968.0           011
>         15968.0           012
>         15968.0           057
>         15968.0           006
>         15968.0           009
>         15968.0           010
>              ..
>              ..
> I would here want to update the entire column A with a list of values that I
> have.(
> 06959.0,15308.0,15968.0,18916.2,19961.0,26528.0,29553.0,29872.0,30631.0
> …).How do I accomplish this? Thank you.
>

It looks like you'd need to do something like the following:

UPDATE my_table SET a = 06959.0 WHERE b = '002';
UPDATE my_table SET a = 15308.0 WHERE b = '003';
UPDATE my_table SET a = 15968.0 WHERE b = '004';

etc

Not sure about your column layout though.  The primary key (in this
case your column B) would usually appear as the first column, and does
it always contain numbers?  If so, wouldn't an int be better?

Thom

Re: Need help on updating an entire column with a list of values, I have.

From

Grzegorz Jaśkiewicz

Date:

26 March 2010, 14:25:35

create temporary table, insert your data, and than run update with join against the table you wish to modify. And than drop your temp table.
simple.

Re: Need help on updating an entire column with a list of values, I have.

From

"Timo Klecker"

Date:

26 March 2010, 14:28:56

Hi again,

are there oids in your table or do you have any possibility to assure the mentioned order of your data lines when you do a select?

If you can assure the order, you could use the temp table solution mentioned by Grzegorz Jaśkiewicz.

If you cannot assure the order this could get tricky…

Mit freundlichen Grüßen

Timo Klecker

Von: Rajan, Pavithra [mailto:RAJANP@coned.com]
Gesendet: Freitag, 26. März 2010 15:17
An: Timo Klecker; pgsql-general@postgresql.org
Betreff: RE: [GENERAL] Need help on updating an entire column with a list of values, I have.

Hello ,

Yes -I need to get the exact the same result as you had listed.Thanks.

Hello,

what do you expect as Result? Something like this?

Mit freundlichen Grüßen

Timo Klecker

Re: Need help on updating an entire column with a list of values, I have.

From

Szymon Guz

Date:

26 March 2010, 14:37:57

W dniu 26 marca 2010 15:21 użytkownik Grzegorz Jaśkiewicz <gryzman@gmail.com> napisał:

create temporary table, insert your data, and than run update with join against the table you wish to modify. And than drop your temp table.
simple.

It would be a nice solution, assuming that we know anything about the correlation between the new values list and the B column values. In fact we have some values in a random order in the database and a list of values that isn't correlated with the database values.

regards

Szymon

Re: Need help on updating an entire column with a list of values, I have.

From

Grzegorz Jaśkiewicz

Date:

26 March 2010, 14:40:18

you can't really do any updates sensibly unless you know what the relation is. So, I kind of silently assume that you know that.

Re: Need help on updating an entire column with a list of values, I have.

From

"Timo Klecker"

Date:

26 March 2010, 14:51:31

Hello,

you could use an plpgsql function:

CREATE OR REPLACE FUNCTION update(numeric[])

RETURNS void AS

$BODY$

declare

data alias for $1;

table_obj record;

I integer;

Begin

i:=0;

for table_obj in execute select * from TABLENAME order by THE_ORDER loop

update TABLENAME set A = data[i] where B = table_obj.B limit 1;

I := I + 1;

end loop;

end;

$BODY$

LANGUAGE 'plpgsql' IMMUTABLE STRICT

And call it with:

Select update(‘{06959.0,15308.0,15968.0,18916.2,19961.0,26528.0,29553.0,29872.0,30631.0}‘)

PS: written code was not tested!

Mit freundlichen Grüßen

Timo Klecker

Hello ,

Yes -I need to get the exact the same result as you had listed.Thanks.

Hello,

what do you expect as Result? Something like this?

Mit freundlichen Grüßen

Timo Klecker

Re: Need help on updating an entire column with a list of values, I have.

From

"Rajan, Pavithra "

Date:

26 March 2010, 14:56:08

Yes thanks -I am trying to figure writing out a script that will do the update than doing individual inserts or update.I'll try this idea.

From: Timo Klecker [mailto:klecker@decoit.de]
Sent: Friday, March 26, 2010 10:51 AM
To: Rajan, Pavithra ; pgsql-general@postgresql.org
Subject: AW: [GENERAL] Need help on updating an entire column with a list of values, I have.

Hello,

you could use an plpgsql function:

CREATE OR REPLACE FUNCTION update(numeric[])

RETURNS void AS

$BODY$

declare

data alias for $1;

table_obj record;

I integer;

Begin

i:=0;

for table_obj in execute select * from TABLENAME order by THE_ORDER loop

update TABLENAME set A = data[i] where B = table_obj.B limit 1;

I := I + 1;

end loop;

end;

$BODY$

LANGUAGE 'plpgsql' IMMUTABLE STRICT

And call it with:

Select update({06959.0,15308.0,15968.0,18916.2,19961.0,26528.0,29553.0,29872.0,30631.0})

PS: written code was not tested!

Mit freundlichen Grüßen

Timo Klecker

Hello ,

Yes -I need to get the exact the same result as you had listed.Thanks.

Hello,

what do you expect as Result? Something like this?

Mit freundlichen Grüßen

Timo Klecker

Re: Need help on updating an entire column with a list of values, I have.

From

"Rajan, Pavithra "

Date:

26 March 2010, 16:34:57

Hello all - Thanks for all your inputs and Klecker's script.Slightly tweaked the script, with Bryan's help to implement it.

CREATE OR REPLACE FUNCTION update(numeric[])

RETURNS void AS

$BODY$

declare

data alias for $1;

table_obj record;

I integer;

Begin

i:=1; //index sarted from 1.

for table_obj in execute select * from TABLENAME order by THE_ORDER loop

update TABLENAME set A = substring('0' || data[i]::varchar from length(data[i]::varchar)-5) where B = table_obj.B limit 1; // had to do this as datatype integer was cutting out leading zeros in entries.

I := I + 1;

end loop;

end;

$BODY$

LANGUAGE 'plpgsql';

Select update({06959.0,15308.0,15968.0,18916.2,19961.0,26528.0,29553.0,29872.0,30631.0})

Rgds

Pavithra

Hello,

you could use an plpgsql function:

CREATE OR REPLACE FUNCTION update(numeric[])

RETURNS void AS

$BODY$

declare

data alias for $1;

table_obj record;

I integer;

Begin

i:=0;

for table_obj in execute select * from TABLENAME order by THE_ORDER loop

update TABLENAME set A = data[i] where B = table_obj.B limit 1;

I := I + 1;

end loop;

end;

$BODY$

LANGUAGE 'plpgsql' IMMUTABLE STRICT

And call it with:

Select update({06959.0,15308.0,15968.0,18916.2,19961.0,26528.0,29553.0,29872.0,30631.0})

PS: written code was not tested!

Mit freundlichen Grüßen

Timo Klecker

Hello ,

Yes -I need to get the exact the same result as you had listed.Thanks.

Hello,

what do you expect as Result? Something like this?

Mit freundlichen Grüßen

Timo Klecker