Re: Separating data sets in a table

On 26 Aug 2002, Oliver Elphick wrote:

> Create a temporary table (no constraints)
>
> CREATE TEMP TABLE temptable AS
> (SELECT * FROM tablename LIMIT 1);
> DELETE FROM temptable;
>
> Copy all data into the temporary table
>
> COPY temptable FROM 'filepath';
Up to this point I have no problems. The difference is that I have to
mark the invalid data sets by a flag which represents a "reason" why
the data were invalid. That's why I use an additional flag in the
table and I do not use a temporary table because I have to store the
"histrory" of invalid data (to bother the provider of the data to
fix it).

> Select from the temporary table all items that satisfy the
> constraints, insert them into the real table and delete them from
> the temporary table:
>
> BEGIN;
> INSERT INTO tablename (SELECT * FROM temptable WHERE ...);
> DELETE FROM temptable WHERE ...;
> COMMIT;
>
> All good data should now be in place. The temporary table should
> now contain only those items that do not satisfy the constraints for
> the real table.
This was in my first atempt here.
The problem I have is that I need a JOIN to a further table and
I've got errors from the parser which let me guess that joins are not
allowed in INSERT statements ... at least I do not know how to do it
right if it should be possible. That's why I had the idea just to
set a certain flag and then do the insert of all data sets where flag = OK.

Kind regards

Andreas.