Re: Copy back-up data into an existing table

DA Morgan wrote:
>
> Ed Prochak wrote:
>
> > 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
>
> And if Ed's email isn't sufficient tell him he can contact the instructor of the
> Oracle Application Development certificate program at the University of
> Washington.
>
> About the only thing as common as flawed designs ... are people that think it is
> better to suffer with them than fix them. And they are almost always incorrect.
>
> Daniel Morgan

Dan and Ed:

   Thanks both of you. I totally agree with you. As a matter a fact, I told my boss's boss one and half year ago, we needed ousider to audit or at least to give second look of our design, just like those practices in the Accounting business. However, I never heard since then. I guess for the bigger sake of business prospect, this suggestion is not part of the schedule. Especially, in our business, the "EXPERIENCED" senior employee has better understanding what a business structure should be. Therefore, their design is always CORRECT.

C Chang Received on Sun Mar 02 2003 - 22:02:23 CST