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.
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... |