Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.misc -> Re: Updating data in table depending on data in same table

Re: Updating data in table depending on data in same table

From: Ed Prochak <>
Date: 9 Feb 2007 06:10:26 -0800
Message-ID: <>

On Feb 9, 6:21 am, "The J Man" <> wrote:
> On Feb 8, 8:53 pm, "Ed Prochak" <> wrote:

> Sorry, my explanation was not too clear and i was using sample data
> rather that the actual data, my bad.
> Table A is basically a list of English terms. Each term has a number
> of translations it needs to be translated into. This entry goes
> through a few status changes and when it gets to approved a row is
> created in table B for each language with the SOURCEID of the English
> term. Table A is manually added. Table B is automatically populated
> and then manually edited.( i.e. the translation of the actual English
> term is added)
> > The first error this shows is that using an ID column as the PK buys
> > you ZERO data integrity.
> > The second error this shows is tha application fails to find the
> > related data in table A.
> > yes you have a real mess.
> Well not really, because we may want duplicate entries in the table as
> long as they have a valid reason for doing so. This is why this
> problem occurred.
> When the user enters her row she is warned that an entry for this
> already exists and asks her are they sure they want to add the new
> row. This is for the scenario where an English word may have different
> contexts in different languages. I may have given the wrong impression
> of this as i did not clearly explain what i was actually doing, i
> tried to ask a generic question.

> > so this was either a bulk load, or manual data entry over a period of
> > time.
> > in either case the process is flawed.
> It was a manual data entry over time. Could you tell me why it is
> flawed?

it is flawed because the design allows these duplicates. A better design would have dealt with this diferently. Not knowing all your business requirements I won't suggest a solution here (and I don't have one off the top of my head). Another easy to point out flaw is the DEPARTMENT attribute in table A. That really should be another table, or (since dups are allowed in this table) multiple rows.

You did not come here for a design review, but a big part of what I posted was just that. I apologize if I offended you.

> > Solutions:
> > 1. A PL/SQL procedure would be the fastest IF you knew PL/SQL.
> I think i will go down this road. I was thinking of using two cursors,
> one to get all entries under 2000, and another to see if there are any
> dupes. If there are, update the rows and then fetch the next row.
> > Given you feel less than familiar with SQL, might I ask how you got
> > this assignment? No one else there knows Oracle?
> Bingo. When i said i was less than familiar, i meant apart from
> creating tables, triggers, sequences etc. I would have no in depth
> experience of using cursors or anything like that. (which is about to
> change!)

Okay, sounds like a good plan. Any assignment where you expand you knowledge is good.
> > You do know how to use an editor like VI with macro commands like
> > global substitute, right?
> > (If you say no to that, then I pity you since you clearly work for an
> > incompetent manager who assigns people to tasks they cannot pereform.)
> Yes, i do know how to that. But i do have an incompetent manager.

Another mea culpa. I assumed you were like some of the posters who can barely write a SELECT query but get assigned projects like this. I still think some table redesign is in order, but that doesn't solve your immediate problem.
> > long term, either change this application or get another job.
> Thank you for your help,
> J.

I hope I did. Now it sounds more like you are a good fit there. so keep the job, and thank you for not taking offense at my less than complimentary remarks.
  Ed Received on Fri Feb 09 2007 - 08:10:26 CST

Original text of this message