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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-sql by date

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