Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAD5bcm54140 for ; Tue, 13 Nov 2001 00:37:38 -0500 (EST) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id fAD5Lae25380 for pgsql-sql@postgresql.org; Tue, 13 Nov 2001 00:21:36 -0500 (EST) (envelope-from news) From: "Jack Gao" X-Newsgroups: comp.databases.postgresql.sql Subject: Is this a bug? Date: Mon, 12 Nov 2001 21:20:44 -0800 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 50 Message-ID: <9sqakt$op1$1@news.tht.net> X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700 To: pgsql-sql@postgresql.org X-Archive-Number: 200111/202 X-Sequence-Number: 5586 Hi, There When I use Update with Join, I got some problem. I have a table as user config table (tblUserConfig), and another table as config item table (tblConfigItem). CREATE TABLE tblUserConfig ( ID integer NOT NULL DEFAULT nextval('tblUserConfig_pKey_ID') PRIMARY KEY, UserID integer NOT NULL, -- tblUser ItemID integer NOT NULL, -- tblConfigItem Value varchar(1024) ); CREATE TABLE tblConfigItem ( ID integer NOT NULL DEFAULT nextval('tblConfigItem_pKey_ID') PRIMARY KEY, Scope integer NOT NULL DEFAULT 1, -- 1: System config, 2: Section config, 3: User config Name varchar(64) NOT NULL, Description varchar(64), DataType integer NOT NULL DEFAULT 1, -- 1: String, 2: Numeric, 3: Boolean, 4: DateTime DefaultValue varchar(1024) ); Now, I would like to update tblUserConfig to set Value of 'UserClassID' to, for example '1'. So I write a sql string like this: Update tblUserConfig Set Value = '1' From tblUserConfig As a Join tblConfigItem As b On a.ItemID = b.ID And b.Scope = 3 Where a.UserID = 1 And b.Name = 'UserClassID'; It always update all records in tblUserConfig table!!! If I change it to: Update tblUserConfig Set Value = '1' Where UserID = 1 And ItemID = (Select ID From tblConfigItem Where Scope = 3 And Name = 'UserClassID'); It works fine. So, is this a bug? or I make anything wrong? Thanks Noodle