Re: update table a.fkid from table b.pkid, additional information

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 14 Jul 2004 18:21:17 -0700
Message-ID: <1089854495.815714@yasure>

amwi wrote:

> "Turkbear" <john.g_at_dot.spamfree.com> wrote in message
> news:252bf0h8apdrfbvst6iaku8075ta6mv487_at_4ax.com...
>

>>"amwi" <amwi_at_yahoo.com> wrote:
>>
>>
>>>"Turkbear" <john.g_at_dot.spamfree.com> wrote in message
>>>news:0qpaf09se84d9u1vocdbna7v1at0j939sk_at_4ax.com...
>>>
>>>>"amwi" <amwi_at_yahoo.com> wrote:
>>>>
>>>>
>>>>>Forgot to mention:
>>>>>
>>>>>I use Oracle 10.1 and SQL *plus 10.1.
>>>>>
>>>>>Thanks for your help!
>>>>>
>>>>>"amwi" <amwi_at_yahoo.com> wrote in message
>>>>>news:DzaJc.98539$dP1.331950_at_newsc.telia.net...
>>>>>
>>>>>>I have tried to solve this on my own for a long time now, so i

>
> really
>

>>>need
>>>
>>>>>>som help here...
>>>>>>
>>>>>>How do i update table a.fkid from table b.pkid with the following
>>>
>>>"rule".
>>>
>>>>>>The table b.pkid's should be evenly distributed over table a.fkid's.
>>>>>>
>>>>>>Does anyone have a tip on how to solve this?
>>>>>>Thank you.
>>>>>>
>>>>>>
>>>>>
>>>>Define what you mean by 'table b.pkid's should be evenly distributed

>
> over
>

>>>table a.fkid's'..
>>>
>>>>For each a.fkid there should be a <specific> b.pkid ? Answer: yes.
>>>>                                                              <at least

>
> 1>
>

>>>?
>>>
>>>>                                                             < a

>
> random>
>

>>>?
>>>
>>>>
>>>
>>>Table b contains 10 rows with specific id's (primary keys).
>>>Table a contains 10000 rows and contains a fk to table b.id.
>>>The problem is to update the 10000 fk's in table a so that the 10 pk's in
>>>table a is spread evenly into 10000 items in table a.
>>>
>>>That is:
>>>The first id in table b is written to 1000 rows in table a.
>>>The second id in table b is written to the next 1000 rows in table a.
>>>...
>>>The twentieth id in table b is written to the last 1000 rows in table a.
>>>
>>>This is the basic idea and it should be real easy to solve in SQL,
>>>somehow...
>>>
>>>Thank you
>>>
>>
>>Sorry, with a design that odd, I do not think plain Sql can do what you

>
> want ( for example, there is no such thing as the
>

>>first or second or any other ordinal record in an Oracle database , unless

>
> you added a sequence number when the record was
>

>>created ( or later), so a statement like
>>
>>'update table a set fk = ( select second pk from b) where a.row is < 1000'
>>
>>is not valid...
>>
>>
>>
>>Perhaps with Pl/Sql and cursors you could loop through table a and for

>
> each 1000,loop through 1 record at a time for table b)
>

>>As to why you want to do this...????
>>
>>

>
> But there is a sequence number! The pk is created with the help of a
> sequence! Can i then use a statement like the one above? I will look at it
> anyway.
> Thank you for your help!

No SQL statement is going to do what you want unless you want to manually code in 10000 unique row identifiers.

Daniel Morgan Received on Wed Jul 14 2004 - 20:21:17 CDT