Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f2FDsMx06835 for ; Thu, 15 Mar 2001 08:54:22 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from mail-hb.fiber.mxim.com ([204.17.142.143]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f2F6lVx43581 for ; Thu, 15 Mar 2001 01:47:31 -0500 (EST) (envelope-from putsch@fiber.mxim.com) Received: by mail-hb.fiber.mxim.com (Postfix, from userid 1883) id 44A74124C4; Wed, 14 Mar 2001 22:47:29 -0800 (PST) Date: Wed, 14 Mar 2001 22:47:29 -0800 From: Jeff Putsch To: pgsql-sql@postgresql.org Subject: Help with UPDATE syntax Message-ID: <20010314224729.A16150@blue.mxim.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i X-Archive-Number: 200103/305 X-Sequence-Number: 1969 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.