Skip site navigation (1) Skip section navigation (2)

question on UPDATE TABLE

From: Wei Weng <wweng(at)kencast(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: question on UPDATE TABLE
Date: 2002-08-23 19:16:45
Message-ID: 1030130205.29121.8.camel@Monet (view raw or flat)
Thread:
Lists: pgsql-sql
I have this query

UPDATE ONLY ContentSummaries 
SET AccessID = '685d96de-1d09-11b2-beec-0001031d54ce' 
FROM Categories AS cat, ContentTypes AS ct, ContentSummaries AS cs LEFT
OUTER JOIN Publishers AS pb ON (pb.PublisherID = cs.PublisherID) 
WHERE cs.CreationDate IS NOT NULL AND cat.CategoryID = cs.CategoryID AND
ct.ContentTypeID = cs.ContentTypeID AND cat.CategoryID =
'391b11d0-1d09-11b2-a312-0001031d54ce' 

Basically, I want to update contentsummaries table when the contentid
(primary key of contentsummaries table) satisfies certain constraints.

And here is the result of doing a select on the contentsummaries table
with those constraints:

mydatabase => select contentid from Categories AS cat, ContentTypes AS
ct,  ContentSummaries AS cs LEFT OUTER JOIN Publishers AS pb ON
(pb.PublisherID = cs.PublisherID) WHERE cs.CreationDate IS NOT NULL AND
cat.CategoryID = cs.CategoryID AND ct.ContentTypeID = cs.ContentTypeID 
AND  cat.CategoryID = '391b11d0-1d09-11b2-a312-0001031d54ce' ;
              contentid               
--------------------------------------
 3cbda8c0-1d09-11b2-9851-0001031d54ce
 3ca53e3e-1d09-11b2-9851-0001031d54ce
 3c8b50c8-1d09-11b2-83cc-0001031d54ce
 3c72e628-1d09-11b2-83cc-0001031d54ce
 3c590226-1d09-11b2-83cc-0001031d54ce
(5 rows)

So I think, based on the documentation on the update table, I should
then be able to update only those 5 rows (listed above) with my UPDATE
query.

But the UPDATE query updated all rows in contentsummaries table.

mydatabase => UPDATE ONLY ContentSummaries SET AccessID =
'685d96de-1d09-11b2-beec-0001031d54ce' FROM Categories AS cat,
ContentTypes AS ct,  ContentSummaries AS cs LEFT OUTER JOIN Publishers
AS pb ON (pb.PublisherID = cs.PublisherID) WHERE cs.CreationDate IS NOT
NULL AND cat.CategoryID = cs.CategoryID AND ct.ContentTypeID =
cs.ContentTypeID  AND  cat.CategoryID =
'391b11d0-1d09-11b2-a312-0001031d54ce' ;
UPDATE 775


Did I do something wrong somewhere in my query? Or is it not do-able in
current Postgresql?

I am using Postgresql 7.2.1 (For redhat 7.3)

Thanks in advance.


-- 
Wei Weng
Network Software Engineer
KenCast Inc.



Responses

pgsql-sql by date

Next:From: Stephan SzaboDate: 2002-08-23 19:51:40
Subject: Re: question on UPDATE TABLE
Previous:From: Dmitry TkachDate: 2002-08-23 17:57:54
Subject: Re: [GENERAL] 'on delete' rule: bug or feature...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group