Received: from th00.opsion.fr (th00.opsion.fr [195.219.20.10]) by postgresql.org (Postfix) with SMTP id DD5ED476BFD for ; Fri, 12 Apr 2002 06:50:53 -0400 (EDT) Received: from 10.1.1.17 [10.1.1.17] by th00.opsion.fr id 200204121050.31c2; Fri, 12 Apr 2002 10:50:49 GMT Send-By: 202.181.138.12 with Mozilla/4.78 [en] (Win95; U) To: Subject: Problem With A Rule (clear version) From: X-Priority: 3 (normal) MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Date: Fri, 12 Apr 2002 10:50:49 GMT Message-id: <200204121050.31c2@th00.opsion.fr> X-Archive-Number: 200204/147 X-Sequence-Number: 7412 Many thanks for the patience! Tom, I am afraid that this too-complete example makes readers headache. Thank you all! CN =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D CREATE TABLE table1 ( --the invoice table. PRIMARY KEY(column10,column1,column2), column10 VARCHAR(255), --id#=3D'1' column1 VARCHAR(255), --invoice# column2 SMALLINT, --serial number column6 NUMERIC, --amount column7 "char" --indicator: either 'r' or 'E' ); CREATE TABLE table2 ( --the voucher detail table. CONSTRAINT c1 FOREIGN KEY (column10,column1) REFERENCES table3 (column10,column1) ON UPDATE CASCADE ON DELETE CASCADE, PRIMARY KEY (column10,column1,column2), column10 VARCHAR(255), --id#=3D'1' column1 VARCHAR(255), --voucher# column6 "char", --indicator: either '1' or '2' column7 NUMERIC, --amount column8 VARCHAR(255), --matched column for table1.column7 column9 VARCHAR(255) --matched column for table1.column2 ); CREATE TABLE table3 ( --the voucher master table. PRIMARY KEY (column10,column1), column10 VARCHAR(255), --id#=3D'1' column1 VARCHAR(255), --voucher# column4 "char", --status=3D'N' column5 VARCHAR(255) --matched column for table1.column1 ); CREATE RULE rule1 AS ON UPDATE TO table1 DO UPDATE table2 SET column7=3DNEW.column6 WHERE column10=3DNEW.column10=20 AND column1=3D(SELECT column1 FROM table3 WHERE column10=3DNEW.column10 AND column4=3D'N' AND column5=3DNEW.column1) AND column6=3D'2' AND column8=3DOLD.column7 AND column9=3DOLD.column2; db1=3D# SELECT column10,column1,column2,column6,column7 FROM table1; column10 | column1 | column2 | column6 | column7 ----------+----------+---------+---------+--------- 1 | Collect1 | 2 | 11 | r 1 | Collect1 | 1 | 130 | r (2 rows) db1=3D# SELECT column1,column5 FROM table3 WHERE column10=3D'1' AND column4=3D'N' AND column5=3D'Collect1'; column1 | column5 -------------+---------- AC200108083 | Collect1 (1 row) db1=3D# SELECT column10,column1,column6,column7,column8,column9 FROM table2 WHERE column10=3D'1' AND column1=3D(SELECT column1 FROM table3 WHERE column10=3D'1' AND column4=3D'N' AND column5=3D'Collect1') AND column6=3D'2' AND column8=3D'r' AND column9=3D'2'; column10 | column1 | column6 | column7 | column8 | column9 ----------+-------------+---------+-----------+------ ---+--------- 1 | AC200108083 | 2 | 11.000000 | r=20=20=20=20 | 2 (1 row) db1=3D# UPDATE table1 SET column6=3D13 WHERE column2=3D1; Cancel request sent ERROR: Query was cancelled. db1=3D# update table2 set column7=3D13 where column10=3D'1' and column1=3D(SELECT column1 FROM table3 WHERE column10=3D'1' AND column4=3D'N' AND column5=3D'Collect1') and column6=3D'2' and column8=3D'r' and column9=3D1; UPDATE 1 IF rule1 is changed to the following version, then the update to table1 works: CREATE RULE rule1 AS ON UPDATE TO table1 DO UPDATE table2 SET column7=3DNEW.column6 WHERE column10=3DNEW.column10 AND column1=3D'AC200108083'=20 AND column6=3D'2' AND column8=3DOLD.column7 AND column9=3DOLD.column2; -------------------------------------------------------- You too can have your own email address from Eurosport. http://www.eurosport.com