Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

New to Postgres... Updating a column via a join... 1

Status
Not open for further replies.

BrianStL

Programmer
Jun 28, 2006
5
US
Good afternoon!

I have been a SQL Server D.B.A. for the past 7 years and am just now getting my feet wet with Postgres.

Could someone provide some help with this error?

ERROR: table name "ameren_sbc_attachments" specified more than once
-------------------
This is how I wrote the query: (and how I would have written it in Query Analyzer)
-------------------
UPDATE public.ameren_sbc_attachments SET Status = "ABANDONED"

FROM public.ameren_sbc

INNER JOIN public.ameren_sbc_attachments ON (public.ameren_sbc."UID"=public.ameren_sbc_attachments."FID")

WHERE

(public.ameren_sbc."Municipal" = 'ROYAL') AND
(public.ameren_sbc_attachments."Owner" = 'INSIGHT')


THANK YOU!
 
Hi

You can [tt]join[/tt] like that only more [tt]from[/tt] tables between them. The condition related to the [tt]update[/tt] table must be in the [tt]where[/tt] part.
Code:
UPDATE public.ameren_sbc_attachments SET Status = "ABANDONED"

FROM public.ameren_sbc

WHERE public.ameren_sbc."UID"=public.ameren_sbc_attachments."FID" and
(public.ameren_sbc."Municipal" = 'ROYAL') AND
(public.ameren_sbc_attachments."Owner" = 'INSIGHT')

Feherke.
 
Thank you VERY much for your reply.

Unfortunately, I now get this error message:

ERROR: column "ABANDONED" does not exist

I tried single quotes as well as double quotes...

I will try fully qualifying by using server.table.column name
 
Hi

Sorry, was in a hurry and did not observed that. In SQL the string values must be enclosed between single quotes ( ' ), not double quotes ( " ) as you wrote.

Double quotes are used to enclose object names which contain characters usually not allowed in identifiers. As you object names contain only letters, you probably do not need then neither there.

And about qualifying names, I never done such thing. Also I find that more parenthesis then necessary could mess up the code.

Arranged in my style :
Code:
update ameren_sbc_attachments
set status = 'ABANDONED'

from ameren_sbc

where ameren_sbc.UID=ameren_sbc_attachments.FID
and ameren_sbc.Municipal = 'ROYAL'
and ameren_sbc_attachments.Owner = 'INSIGHT'

Feherke.
 
Here is my new error message:

ERROR: column public.ameren_sbc.uid does not exist

Sorry if I sound like a fool, but how could this be? That column ABSOLUTELY exists in my table?!?! LOL!

Here is the code I was using to execute the UPDATE:

update public.ameren_sbc_attachments
set status = 'ABANDONED'

from public.ameren_sbc

where public.ameren_sbc.UID = public.ameren_sbc_attachments.FID
and public.ameren_sbc.Municipal = 'ROYAL'
and public.ameren_sbc_attachments.Owner = 'INSIGHT'
 
Is this what you were referring to?

FOREIGN KEY ("FID")
REFERENCES "public"."ameren_sbc"("UID")
ON DELETE CASCADE
ON UPDATE CASCADE
NOT DEFERRABLE
 
TABLE: ameren_sbc_attachments
TYPE: ameren_sbc_attachments
INDEX: ameren_sbc_attachments_owner
INDEX: ameren_sbc_attachments_pkey
INDEX: ameren_sbc_attachments_type
SEQUENCE: ameren_sbc_attachments_UID_seq
Type: ameren_sbc_attachments_UID_seq

TABLE: ameren_sbc
TYPE: ameren_sbc
INDEX: ameren_sbc_municipal
INDEX: ameren_sbc_pkey
SEQUENCE: ameren_sbc_UID_seq
TYPE: ameren_sbc_UID_seq
 
Hi

No. I meant the output of this :
Code:
\d ameren_sbc
\d ameren_sbc_attachments
If there are no sensitive data, maybe this too :
Code:
select * from ameren_sbc limit 3;
select * from ameren_sbc_attachments limit 3;

Feherke.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top