I have tried to solve this on my own for a long time now, so i really need
some help here...

I use Oracle 10.1 and SQL *plus 10.1.

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 as fkid's.

Does anyone have a tip on how to solve this?

Thank you.

"amwi" <amwi (AT) yahoo (DOT) com> wrote


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

"amwi" <amwi (AT) yahoo (DOT) com> wrote

You should ask your professor for help. If you want help with homework
in this group, you should:
try to write the SQL on your own.
If you cannot do it, post what you tried and we will lend some
guidance.
(we will not do your homework for you.)

Here's a hint via a question: Is there a way, using the current
columns and data in table a to groups the values in sets of 1000?

IOW can a query like

SELECT count(*),<some column list>
FROM table a
GROUP BY <some column list> ;

result in 10 rows with counts of about 1000 for each?

HTH,
ed