RE: Help with UPDATE syntax

Try eliminating the statement " from user_group_map map".  It does not belong in the update.  Here is the fully
rewrittenstatement:
 

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_idand                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
oftransaction block 
 

As noted earlier, any guidance will be most appreciated.

Thanks,

Jeff.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster