Re: SQL problem

Hi Russ,

    Why bother?

    If I understand correctly you now have a table with a column that has duplicate values and wish to copy it to a new table that requires unique values in the same column.

    If this is so why not just:

    Step 1:

        CREATE new_table AS SELECT * FROM old_table;

    Step 2:

        DELETE FROM new_table
        (SELECT *
        FROM old_table
        WHERE  ROWID <> ( SELECT MAX(ROWID)
                    FROM old_table B
                    WHERE  b.dup_column = old_table.dup_column);

Step 3:

        CREATE UNIQUE INDEX new_table_ix1 ON new_table(dup_column);

regards

Jerry

Russell Fray wrote in message <35e6cb91.185646196_at_news.u-net.com>...
>
>Hi there,
>
>Just a quick question for the SQL gurus, as I can't seem to suss this
>one :)
>
>I have a table which contains lots of numbers in a field called
>'ag_num'. Within this field, some numbers are duplicated. I want to
>list the duplications so i can delete them at my discretion, before
>copying all data from this table in to a new table that does not allow
>duplicated in that field.
>
>In pseudo code :
>
>select * from (table.name) where the same ag_num is repeated more than
>once
>
>Please cc replies to russ_at_u-net.net
>
>Thanks!
>Russ.
>
Received on Tue Sep 01 1998 - 11:56:15 CDT