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

"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...???? Received on Wed Jul 14 2004 - 14:30:51 CDT