pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp 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