db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dag.Wan...@Sun.COM (Dag H. Wanvik)
Subject Re: how to suppress similar rows while copying from one table to another?
Date Sat, 17 Nov 2007 01:37:27 GMT
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

Mime
View raw message