Need help on updating an entire column with a list of values, I have.
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.
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.
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.
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
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.
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.
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
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.
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.
_____
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.
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.
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
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.
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,306
31.0})
PS: written code was not tested!
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.
_____
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.
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.
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
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.
________________________________
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.
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.
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
________________________________
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
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.
________________________________
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.
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.