pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Davis <mdavis@sevainc.com>
To: 'Jeff Putsch' <putsch@mxim.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: RE: Help with UPDATE syntax
Date: Thu, 15 Mar 2001 08:59:27 -0700
Message-ID: <01C0AD2E.3F32AB40.mdavis@sevainc.com> (raw)

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




view thread (4+ messages)  latest in thread

Message-ID: <01C0AD2E.3F32AB40.mdavis@sevainc.com>
Permalink:  ../01C0AD2E.3F32AB40.mdavis@sevainc.com/
Also on:    postgresql.org/message-id/01C0AD2E.3F32AB40.mdavis@sevainc.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mdavis@sevainc.com, putsch@mxim.com
  Subject: Re: Help with UPDATE syntax
  In-Reply-To: <01C0AD2E.3F32AB40.mdavis@sevainc.com>
  References: <01C0AD2E.3F32AB40.mdavis@sevainc.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox