Thread: 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.
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
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
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