Good afternoon:
I would like to insert some (1 or more) values from multiple rows of one table into an array in another table. Here's the scenario:
--table to house data provided by a third party
CREATE TABLE raw_foo (
rf_id serial PRIMARY KEY,
cde character varying(4),
nbr integer,
aaa character varying(60),
bbb character(10),
ccc character varying(20)
);
--table raw_foo populated by copying from a text file
--columns cde||nbr identify a person while columns aaa||bbb||ccc describe an attribute of a person
--since each person can have one or more attributes, the cde||nbr identifier is not distinct
--need data in raw_foo flattened so that there is only one record per person
--second table in which aaa, bbb, and ccc are array fields
CREATE TABLE foo_arrays (
cde character varying(4),
nbr integer,
aaa text[],
bbb text[],
ccc text[],
PRIMARY KEY (cde, nbr)
);
--insertion of all distinct cde||nbr combinations from raw_foo
INSERT INTO foo_arrays
(cde, nbr)
(SELECT cde, nbr
FROM raw_foo
GROUP BY cde, nbr
HAVING COUNT(*) = 1)
UNION
(SELECT cde, nbr
FROM raw_foo
GROUP BY cde, nbr
HAVING COUNT(*) > 1);
--hope to update foo_arrays.aaa by selecting every instance of raw_foo.aaa where raw_foo.cde||raw_foo.nbr matches the distinct value of foo_arrays.cde||foo_arrays.nbr (repeating the process for foo_arrays.bbb and foo_arrays.ccc)
UPDATE foo_arrays
SET aaa = ???
This is where I'm stumped.
Am I on the right path?
Thanks in advance.
Cheers,
Rob
For up-to-date information about H1N1 Swine Flu visit http://www.myflusafety.com or call 877 352 3581
