Received: from mail01.phoenixdsl.com (mail.phoenixdsl.com [216.178.151.24]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2FFxZx22477 for ; Thu, 15 Mar 2001 10:59:36 -0500 (EST) (envelope-from mdavis@sevainc.com) Received: (qmail 37632 invoked by uid 89); 15 Mar 2001 15:59:31 -0000 Received: from unknown (HELO SALLY) (64.32.157.45) by 0 with SMTP; 15 Mar 2001 15:59:31 -0000 Received: by localhost with Microsoft MAPI; Thu, 15 Mar 2001 08:59:30 -0700 Message-ID: <01C0AD2E.3F32AB40.mdavis@sevainc.com> From: Michael Davis Reply-To: "mdavis@sevainc.com" To: "'Jeff Putsch'" , "pgsql-sql@postgresql.org" Subject: RE: Help with UPDATE syntax Date: Thu, 15 Mar 2001 08:59:27 -0700 Organization: Seva Inc. X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/309 X-Sequence-Number: 1973 Try eliminating the statement " from user_group_map map". It does not belong in the update. Here is the fully rewritten statement: update user_group_map set user_id = 4 where user_id = 9 and not exists ( select * from user_group_map ug2 where user_id = 4 and ug2.group_id = map.group_id and ug2.role = map.role); -----Original Message----- From: Jeff Putsch [SMTP:putsch@mxim.com] Sent: Wednesday, March 14, 2001 11:47 PM To: pgsql-sql@postgresql.org Subject: Help with UPDATE syntax Howdy, I am porting a bit of code from ORACLE to PostgreSQL 7.1 and am getting stuck on an update statment. I am NOT a SQL expert, merely a beginner swimming hard, so any help will be greatly appreciated. The specific query looks like this: begin transaction update user_group_map map set user_id = 4 where user_id = 9 not exists ( select * from user_group_map where user_id = 4 and group_id = map.group_id and role = map.role ) commit There are other updates taking place during the transaction, but this is the one for which I can't figure out the PostgreSQL equivalent. I've tried this: update user_group_map set user_id = 4 from user_group_map map where user_id = 9 and not exists ( select * from user_group_map ug2 where user_id = 4 and ug2.group_id = map.group_id and ug2.role = map.role); for the update replacement, but get an error: NOTICE: current transaction is aborted, queries ignored until end of transaction block As noted earlier, any guidance will be most appreciated. Thanks, Jeff. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster