pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help with UPDATE syntax
4+ messages / 4 participants
[nested] [flat]

* Help with UPDATE syntax
@ 2001-03-15 06:47  Jeff Putsch <putsch@mxim.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Jeff Putsch @ 2001-03-15 06:47 UTC (permalink / raw)
  To: pgsql-sql

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.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Help with UPDATE syntax
@ 2001-03-15 15:59  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jeff Putsch <putsch@mxim.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 2001-03-15 15:59 UTC (permalink / raw)
  To: Jeff Putsch <putsch@mxim.com>; +Cc: pgsql-sql

Jeff Putsch <putsch@mxim.com> writes:
>     update   
>         user_group_map map 

Postgres doesn't allow UPDATE to use an alias for the target table
(SQL92 doesn't either).  Get rid of the alias name "map", and write
the full table name "user_group_map" in the places where "map" is
used in the WHERE clause.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* RE: Help with UPDATE syntax
@ 2001-03-15 15:59  Michael Davis <mdavis@sevainc.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Michael Davis @ 2001-03-15 15:59 UTC (permalink / raw)
  To: 'Jeff Putsch' <putsch@mxim.com>; pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: RE: Help with UPDATE syntax
@ 2001-03-15 20:33  Richard Huxton <dev@archonet.com>
  parent: Michael Davis <mdavis@sevainc.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Richard Huxton @ 2001-03-15 20:33 UTC (permalink / raw)
  To: mdavis@sevainc.com; 'Jeff Putsch' <putsch@mxim.com>; pgsql-sql

From: "Michael Davis" <mdavis@sevainc.com>

> 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);
                                 ^^^
I take it these are actually "user_group_map"?

- Richard Huxton




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-03-15 20:33 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-15 06:47 Help with UPDATE syntax Jeff Putsch <putsch@mxim.com>
2001-03-15 15:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-15 15:59 RE: Help with UPDATE syntax Michael Davis <mdavis@sevainc.com>
2001-03-15 20:33 ` Richard Huxton <dev@archonet.com>

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