Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem UPDATE Statement

Problem UPDATE Statement

From
Date:
i'm trying to apply the following UPDATE statement:

UPDATE t_sn
SET t_sn.sn_completed = false
WHERE sn = 1
AND t_sn.link_id = t_link.link_id
AND t_link.job_id = t_job.job_id
AND t_job.product_id = t_product.product_id
AND t_product.product_id = 118

i get the following error:

ERROR:  missing FROM-clause entry for table "t_link"

i'm assigning the two values that i know (sn (serial
number) and product_id) and then i'm trying to connect
via my foreign/primary key definitions.  i have the
"logic" to this point.  i ahve some books, but they
tend to have simple examples - nothing like doing an
update and traversing across 4 tables to get the data
required (in my case - t_sn, t_link, t_job,
t_product).

i'm a little frustrated b/c my paradigm is obviously
wrong, but it isn't obvious to me what i'm doing
wrong.  it doesn't help that is a different error each
time, either.

as always, any help/guidance is greatly appreciated.



__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com

Re: Problem UPDATE Statement

From
Tom Lane
Date:
<operationsengineer1@yahoo.com> writes:
> i'm trying to apply the following UPDATE statement:
> UPDATE t_sn
> SET t_sn.sn_completed = false
> WHERE sn = 1
> AND t_sn.link_id = t_link.link_id
> AND t_link.job_id = t_job.job_id
> AND t_job.product_id = t_product.product_id
> AND t_product.product_id = 118

> i get the following error:

> ERROR:  missing FROM-clause entry for table "t_link"

You need a "FROM t_link" in there.  Prior versions of PG were laxer
about this, but it's been a very long time since this command wouldn't
draw at least a NOTICE ...

            regards, tom lane

foreign key's in system tables

From
"Tjibbe Rijpma"
Date:
Hi,
 
From a table I want to know all the foreign key en their references. This goes well, but if there is more than one column in a FOREIGN KEY is goes wrong.
I have trouble with the arrays: conkey and confkey. 
 
Can anyone help me?
 
Greetings Tjibbe
 
 
 
CREATE TABLE mens (
 voornaam TEXT,
 achternaam TEXT,
 UNIQUE (voornaam, achternaam)
);
 
CREATE TABLE inwoner (
 inwoner_voornaam TEXT,
 inwoner_achternaam TEXT,
 FOREIGN KEY (inwoner_voornaam, inwoner_achternaam) REFERENCES mens (voornaam, achternaam)
);
 

SELECT a_fk.attname as fk_column, ref.relname as ref_table, a_ref.attname as ref_column
FROM pg_constraint c, pg_class fk, pg_attribute a_fk, pg_class ref,  pg_attribute a_ref
WHERE c.conrelid = fk.oid
AND a_fk.attrelid = fk.oid
AND c.confrelid = ref.oid
AND a_ref.attrelid = ref.oid
AND c.contype = 'f'
AND fk.relname = 'inwoner'
AND a_fk.attnum = ANY (c.conkey)
AND a_ref.attnum = ANY (c.confkey)
 
 
Result:
 
fk_column          | ref_table | ref_column
--------------------------------------------
inwoner_voornaam   | mens      | voornaam
inwoner_voornaam   | mens      | achternaam
inwoner_achternaam | mens      | voornaam
inwoner_achternaam | mens      | achternaam

 

Re: Problem UPDATE Statement

From
Date:
> <operationsengineer1@yahoo.com> writes:
> > i'm trying to apply the following UPDATE
> statement:
> > UPDATE t_sn
> > SET t_sn.sn_completed = false
> > WHERE sn = 1
> > AND t_sn.link_id = t_link.link_id
> > AND t_link.job_id = t_job.job_id
> > AND t_job.product_id = t_product.product_id
> > AND t_product.product_id = 118
>
> > i get the following error:
>
> > ERROR:  missing FROM-clause entry for table
> "t_link"
>
> You need a "FROM t_link" in there.  Prior versions
> of PG were laxer
> about this, but it's been a very long time since
> this command wouldn't
> draw at least a NOTICE ...
>
>             regards, tom lane

Tom, thanks.  that was it.  the basic examples didn't
include the FROM clause - i surmise it isn't required
until you start traversing across linked tables.

this worked...

UPDATE t_sn
SET sn_completed = false
FROM t_link, t_job, t_product
WHERE sn = 1
AND t_sn.link_id = t_link.link_id
AND t_link.job_id = t_job.job_id
AND t_job.product_id = t_product.product_id
AND t_product.product_id = 118

thanks again.



__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com

Re: foreign key's in system tables

From
Michael Fuhr
Date:
On Wed, Jan 04, 2006 at 11:57:45PM -0800, Tjibbe Rijpma wrote:
> From a table I want to know all the foreign key en their references.
> This goes well, but if there is more than one column in a FOREIGN KEY
> is goes wrong.

Are you familiar with the pg_get_constraintdef() function?  That's
what psql's "\d tablename" command uses.

http://www.postgresql.org/docs/8.1/interactive/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE

--
Michael Fuhr