Received: from www-uk-05.digiserve.com ([195.172.23.32]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA56272 for ; Wed, 17 May 2000 15:48:52 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id UAA15625; Wed, 17 May 2000 20:48:19 +0100 (BST) Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 970402A0FB; Wed, 17 May 2000 20:45:37 +0100 (BST) Message-ID: <007f01bfc038$7842ab80$1001a8c0@archonet.com> From: "Richard Huxton" To: "Kyle Bateman" Cc: References: <3921E506.82424CAF@actarg.com> <200005171352.HAA19867@biology.nmsu.edu> <3922B7A2.5BEF5F5E@actarg.com> Subject: Re: question on update/delete rules on views Date: Wed, 17 May 2000 20:45:32 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 X-Archive-Number: 200005/35 ----- Original Message ----- From: Kyle Bateman To: Brook Milligan Cc: Sent: Wednesday, May 17, 2000 4:15 PM Subject: Re: [SQL] question on update/delete rules on views > Brook Milligan wrote: > > > create rule view_a_r_update as on update to view_a > > do instead > > update a set two = new.two; > > > > The problem is that your INSTEAD UPDATE rule is not constrained in any > > way; it DOES hit every row. Instead, do something like: > > > > create rule view_a_r_update as on update to view_a > > do instead > > update a set two = new.two > > where id = old.id; > > > > where id is a primary key in your table. > > > > Thanks for the help. The problem with your suggestion is the view has to > anticipate which column(s) the calling query wants to look at. What if > the calling query has not specified the primary key in its where clause? > In our real case, the table has many columns. There are a variety of > queries that act on the table based on a variety of conditions in a > variety of columns. I'd like to avoid having to have a separate rule or > view for every possible where combination. Maybe that is not possible, > but the manual seems to say it should work, so that's why I'm asking the > question. > AFAIK it doesn't matter if the original query used a field - the "old" and "new" in the rule represent the row being updated (before and after) - you can access any column. -- Richard Huxton