On Thu, Nov 1, 2018 at 1:26 PM Rich Shepard <rshepard@xxxxxxxxxxxxxxx> wrote:
I have the following code in a script:
alter table stations add column start_date date;
alter table stations add column end_date date;
alter table stations add column howmany integer;
alter table stations add column bin_col char(8);
insert into stations (start_date, end_date, howmany, bin_col) values ( )
select site_nbr from stations
where site_nbr = ' ';
The table has 82 rows. Is there a more elegant way to insert data specific
to a site_nbr other than 82 repetitions of the insert statement? (I suspect
not, but I might be wrong and learn something valuable by asking.)
That makes no sense to me...you already have 82 rows on the table so if you insert 82 more you'll have 164 which doesn't seem like what you would want...
I would probably do:
CREATE TABLE stations_ext (site_nbr, start_date date, ...)
COPY stations_ext FROM filename;
UPDATE stations SET start_date = stations_ext.start_date, ...
FROM stations_ext
WHERE stations.site_nbr = stations_ext.site_nbr;
OR
UPDATE stations SET start_date = 'literal date'::date WHERE site_nbr = 'literal site number';
... 82 more times as appropriate
But I would build out those UPDATE statements in a spreadsheet
Either way I'd get the relevant new data into tabular format with a site_nbr associated first.
David J.
