On Wednesday 21 April 2004 21:07, Kemin Zhou wrote: > Here I have a very simple case > > table1 > table1_removed > > anotherTable > > create or replace RULE rec_remove as ON DELETE TO table1 > do insert into table1_remove > select old.*, a.acc from old g join anotherTable a on g.acc=a.other_acc; > === > the parser complained ERROR: relation "*OLD*" does not exist > So I used > select old.*, a.acc from table1 g join anotherTable a on g.acc=a.other_acc; > > This worked find. > > When I run delete on table1, 213 rows. > > tmp table received 213X213 = 45369 rows. each row is duplicated 213 times.
The issue here is that although you can refer to values such as OLD.acc, OLD is not a table but more like single row. So, you probably want ...DO INSERT INSTO table1_remove SELECT old.*, a.acc FROM anotherTable a WHERE a.other_acc = OLD.acc; Your second example just ignored the OLD.acc altogether in the join, so of course you got an unconstraind join of 213 x 213. -- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
