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

From: Wit Serdakovskij <wit_no_at_spam_dba.kiev.ua>
Date: 15 Jul 2004 10:17:57 GMT
Message-ID: <20040715101758.GF5337@tormoz.net>

Hello, amwi,

near 19:12 14-Jul from amwi_at_yahoo.com:
[...]
> > >"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.
[...]

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

create table t1 (c1 number primary key); insert into t1 select object_id
from user_objects
where rownum < 11;

create view vt1 as select rownum-1 c0, c1 from t1;

create table t2 (c1 number primary key, c2 number); /* c2 is for fk */
insert into t2(c1) select rownum from all_tab_columns;

create view vt2 as select rownum-1 c0, c1, c2 from t2;

create trigger tr_vt2 instead of
update
on vt2
begin
update t2 set c2 = :new.c2
where c1 = :new.c1;
end;
/

/* Take a look at "10" - this is a count of rows in t1 */ update vt2 set c2 = (select c1 from vt1 where vt1.c0 = mod(vt2.c0,10));

select c2, count(*) from t2 group by c2;

HTH. Thanks for very interesting question.

--
wbr,
Wit.

P.S.: Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE    9.2.0.3.0       Production

Received on Thu Jul 15 2004 - 05:17:57 CDT