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

On Feb 8, 8:53 pm, "Ed Prochak" <edproc..._at_gmail.com> wrote:
> On Feb 8, 2:14 pm, "The J Man" <jonathan.kee..._at_gmail.com> wrote:
>

Thanks for both of the replies...

>
>
> it is not clear how B gets populated from A. Table A doesn't seems
> like a staging table as I first thought. There is obviously more going
> on in the process. So is it manual or automated? What are the rules?
>

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 go to your backup from the night before and start over. reload
> those two tables and tell the entry person to do it right this time
> (it's actually not her fault, but she still has to reenter any data
> from that day.) You do do backups don't you?
> (By now this solution is too late, you let too much other stuff get
> inserted. time for plan B)

as you said, too late. Unfortunately.

>
> But the only common attribute is the source ID between table B and
> table A.

I meant common attributes between tuples in table A. So any tuple within table A that has the same name and description.

>
> 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?

> > update TARGET set TARGET.SOURCEID=MASTER_NUMBER where
> > TARGET.SOURCEID=DUPLICATE_NUMBER;
> > update SOURCE set
> > SOURCE .LANGSREQUIRED='French;Spanish;German;Italian;Brazilian
> > Portuguese;Simplified Chinese;Traditional Chinese;Swedish;French
> > Canadian;Dutch;Korean;Japanese;' where SOURCE .SOURCEID=MASTER_NUMBER;
> > DELETE FROM SOURCE WHERE SOURCEID= DUPLICATE_NUMBER;
>
> Where's table A and table B??? which is which??
>

Apologies, i copied that straight from my SQL Developer, table A is SOURCE and table B is TARGET.

> 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!)

> 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.

>
> long term, either change this application or get another job.
>

Thank you for your help,
J. Received on Fri Feb 09 2007 - 05:21:51 CST