Subject: question on UPDATE TABLE
From: Wei Weng <wweng@kencast.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: 23 Aug 2002 15:16:45 -0400
Message-Id: <1030130205.29121.8.camel@Monet>
Mime-Version: 1.0

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.