Re: copy row tree

On 1/17/07, Richard Huxton <dev(at)archonet(dot)com> wrote:
> Toni Casueps wrote:
> >
> > I have a set of tables with one-to-many relationships between them:
> >
> > T1 <-->> T2 <-->> T3 <-->> T4
> >
> > I need to copy some rows of these tables to another set of tables which
> > have the same fields.
>
> There's no shortcut.
>
> BEGIN;
> INSERT INTO copy_t1 SELECT * FROM t1 WHERE id IN (123, 456);
> INSERT INTO copy_t2 SELECT * FROM t2 WHERE t2_t1_ref IN (SELECT id FROM
> t1 WHERE id IN (123, 456));
> INSERT INTO copy_t3 SELECT * FROM t3 WHERE t3_t2_ref IN (SELECT ... FROM
> t2 WHERE ...)
> ...etc...
> COMMIT;

maybe there is:
set up RI triggers and set them to on update..cascade
add a trigger to each table which inserts into copy tables
if head table has p-key of id,

update main set id = id where id in (copy list);

:-)

merlin