Re: Copy back-up data into an existing table

C Chang wrote:
{follow back in the thread for previous discussion]
> Ed:
> Thanks for your suggestion. I am not the original designer of this
> Database. I am not even the DBA or Oracle developer for our project
> here. I am just 1/3 tester, 1/3 web developer, 1/3 Host administor, well
> maybe 1/6 of SQL developer. ( that's why I asked questions in 6at least
> 7 different news groups), So i have no right to change the structure.
> Because there is a problem, my boss told me to fix it.
>
> Anyway. I found the sql syntax to delete the duplicate ones. But somehow
> it did not work well. What is happened this afternoon was:
> Say originally
> 1. the table has 1000 rows of record.
> 2. I dump this 1000 rows out -> a dmp file
> 3. I deleted 990 rows out from the table using its date as condition.
> 4. I import the dmp file back using the ignore=y
> 5. Table has 1010 rows now. Supposely 10 duplicate ones.
> 6. I tried the standard syntax of delete duplicated rows
> delete from T t1
> where t1.rowid <> <==== I tried with ">" only as well
> ( select min(t2.rowid)
> from T t2
> where t1.col1 = t2.col1
> and t1.col2 = t2.col2
> and < for rest of all columns, including a object column,
> which I use t1.obj.column to reference> )
> 7. return with 0 rows deleted
>
> then I tried different combindation of those columns. Of course it
> return with different numbers of rows deleted. So I run out of my steam.
> I figured out another way to accomplish my goal. Will try next Monday.
> Does anyone has better idea what was wrong with my syntax above? Thanks
>
> I also tried with "group by all columns", but I got the error
> ORA-22950: cannot ORDER objects without MAP or ORDER
> method. If the table definition did not include a MAP member function
> for a
> user defined object type column. Any method can goes around the current
> infrastructure or I need to change the table definition ( if I can ).
>
> C C
>
>
>
> C Chang

Have you considered that there were duplicates in the table when you started? (ie, at step 1.) I'll bet when you find duplicates you always came up with a count greater than 10.

Tell your boss that the design is flawed. Or to protect yourself, send him a copy of this message. I will gladly tell him the design is flawed. Surely he want results in a timely manner. This problem of duplicates would not exist if there were proper constraints in the system. I'll gladly contract to do some proper design work for him, or, if he prefers, show his developers some proper design (I'll train the developers).

(Yes, I've told people when a flawed design existed. I've taken the heat for it too.)

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost