On Thu, Nov 1, 2018 at 1:26 PM Rich Shepard <rshep...@appl-ecosys.com> 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.
