Re: using a join in an 'INSERT ... SELECT' ...

On Thu, 14 Sep 2000, The Hermit Hacker wrote:

>
> Okay, logically I think this makes sense, but its not working ... should
> it?
>
> globalmatch=# insert into auth_info_new
> globalmatch-# select ai.* from auth_info ai, auth_info_new ain
> globalmatch-# where ai.username != ain.username;
> INSERT 0 0
>
> auth_info has 14k tuples, but some are duplicates ... I want to insert
> into auth_info_new everything except those that have already been inserted
> into auth_info_new ...
>
> now, my first thought looking at the above would be that since
> auth_info_new is empty, all from auth_info should be copied over ...
> basically, since each tuple isn't "committed" until the insert is
> finished, then every username in ai is definitely not in ain ... but
> nothing is being copied over, so my first thought is definitely wrong ...
>
> bug? *raised eyebrow*

Nah. Remember, you're doing a product with that join. If there are no
rows in auth_info_new, there are no rows after the join to apply the where
to.

You really want something like (untested):
insert into auth_info_new
select ai.* from auth_info ai where not exists
( select * from auth_info_new ain where ai.username=ain.username );