Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: domain of amichail@gmail.com designates
 209.85.132.251 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=OeQca4rLCX4midu15XHNDj5zf8/99pA2JCzfaHCLXaM9MD4qQksw2dxDhw1xUBsuhOTOaPD3yIrVe2gDyTzWpk+TjqSuPykwXyr8p9+fgAGIgTHqoQ8FbrdIVZR9ROoBU0c29rrAVAJMZm0vxW69UbitrIvL+o5gD9bKymlQX+0=
Message-ID: <eb9696930711161750y42021696r5a6ab3c8c91edb3d@mail.gmail.com>
Date: Fri, 16 Nov 2007 20:50:45 -0500
From: "Amir Michail" <amichail@gmail.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: how to suppress similar rows while copying from one table to
 another?
In-Reply-To: <x1ox3av53b54.fsf@sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <eb9696930711151702k7c09f142m604c86f2c58f248a@mail.gmail.com>
	 <473D97D7.4020700@sun.com> <473DD4F4.6040405@apache.org>
	 <x1ox3av53b54.fsf@sun.com>

Hi,

I ended up using JDBC to do this.

Amir

On Nov 16, 2007 8:37 PM, Dag H. Wanvik <Dag.Wanvik@sun.com> wrote:
>
> Daniel John Debrunner <djd@apache.org> writes:
>
> > Rick Hillegas wrote:
> >
> >> One way to solve this problem would be to put a filtering function
> >> in the WHERE clause of the driving SELECT statement. Something like
> >> this:
> >> INSERT INTO targetTable
> >> SELECT * FROM sourceTable
> >> WHERE isFirstInstance( tastyColumn1, tastyColumn2 ) = 1
> >> Here isFirstInstance is a function which returns 1 the first time it
> >> sees a given key combination and returns 0 on all subsequent
> >> sightings. You, of course, have to write this user function.
> >
> > Any thoughts on how to do that? Since such calls are stateless how
> > would you identify it's the first call for a query execution?
>
> I first thought of using a tmp table containing just the two columns,
> and just opening a result set on the first table, loop, and copying a
> row to destination table if not present yet in tmp table, else insert
> it there too.  One could hide that check inside a function like Rick
> suggests?
>
> Tried in vain to come up with a clever single INSERT, though.
>
> Dag
>