pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Trouble with insert into.
2+ messages / 2 participants
[nested] [flat]

* Trouble with insert into.
@ 1999-06-18 11:38 Darren Greer <dgreer@websightsolutions.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Darren Greer @ 1999-06-18 11:38 UTC (permalink / raw)
  To: pgsql-sql

Hello all.  Lets say I have:
Table A (username text, first_name text, last_name text, zip int4)
Table B (username text, signupdate date, zip text)

In table A I have
user1	fred	jones	53125
user2	bob	smith	52145

In table B I have
user2	06/16/1999	52145

The way these tables were populated, table A and B were written to at the same
time.  However, table B, did not exist for the first 6 months of use.  So there
are many users who are in table A, that are not in table B.  

Now what I am tryign to do, is insert all users that dont exist in table B,
that exist in table A, into table B.  With o ne twist.  Signup date does not
exist in table A, so I need to insert them all with an arbitrary date.  I plan
on putting them all in as 04/15/1999.

Here is the select statement I was able to get to work:
	select l.username, l.zip
	from tableA l
	where not exists (
		select ld.username
		from tableB ld
		where ld.username = l.username
	);

Now my difficulty comes in inserting the data that is retrieved into table B.

If anyone can help me out, I would greatly appreciate it.  

Thanks,

Darren





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* RE: [SQL] Trouble with insert into.
@ 1999-06-18 14:24 Mathew White <guru@fundgroup.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Mathew White @ 1999-06-18 14:24 UTC (permalink / raw)
  To: 'dgreer@websightsolutions.com' <dgreer@websightsolutions.com>; pgsql-sql

Hi. I'm new to mailing lists, so let me know if I don't post 
properly.

Here is an SQL statement that I found that works for what you are 
doing (your own SQL statement was very close). Note the text 
conversion for the zip field.

insert into tableB
        select l.username, '4-15-1999', l.zip::text
        from tableA l
        where not exists (
                select ld.username
                from tableB ld
                where ld.username = l.username
        )
;

On Friday, June 18, 1999 5:38 AM, Darren Greer 
[SMTP:dgreer@websightsolutions.com] wrote:
> Hello all.  Lets say I have:
> Table A (username text, first_name text, last_name text, zip int4)
> Table B (username text, signupdate date, zip text)
>
> In table A I have
> user1	fred	jones	53125
> user2	bob	smith	52145
>
> In table B I have
> user2	06/16/1999	52145
>
> The way these tables were populated, table A and B were written to
> at the same
> time.  However, table B, did not exist for the first 6 months of
> use.  So there
> are many users who are in table A, that are not in table B.
>
> Now what I am tryign to do, is insert all users that dont exist in
> table B,
> that exist in table A, into table B.  With o ne twist.  Signup date
> does not
> exist in table A, so I need to insert them all with an arbitrary
> date.  I plan
> on putting them all in as 04/15/1999.
>
> Here is the select statement I was able to get to work:
> 	select l.username, l.zip
> 	from tableA l
> 	where not exists (
> 		select ld.username
> 		from tableB ld
> 		where ld.username = l.username
> 	);
>
> Now my difficulty comes in inserting the data that is retrieved 
into
> table B.
>
> If anyone can help me out, I would greatly appreciate it.
>
> Thanks,
>
> Darren
>
>




^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~1999-06-18 14:24 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-18 11:38 Trouble with insert into. Darren Greer <dgreer@websightsolutions.com>
1999-06-18 14:24 RE: [SQL] Trouble with insert into. Mathew White <guru@fundgroup.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox