Thread: question on UPDATE TABLE

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-0001031d54ce3ca53e3e-1d09-11b2-9851-0001031d54ce3c8b50c8-1d09-11b2-83cc-0001031d54ce3c72e628-1d09-11b2-83cc-0001031d54ce3c590226-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.

On 23 Aug 2002, Wei Weng wrote:

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

You do realize you're getting two copies of ContentSummaries in this
update and only one is actually being constrained afaics (and it's not the
one being updated).  Perhaps something like
cs.ContentTypeID=ContentSummaries.ContentTypeID or something like that
in the where clause?

Thanks a lot! It works. :)

On Fri, 2002-08-23 at 15:51, Stephan Szabo wrote:
> On 23 Aug 2002, Wei Weng wrote:
> 
> > 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'
> 
> You do realize you're getting two copies of ContentSummaries in this
> update and only one is actually being constrained afaics (and it's not the
> one being updated).  Perhaps something like
> cs.ContentTypeID=ContentSummaries.ContentTypeID or something like that
> in the where clause?
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 
-- 
Wei Weng
Network Software Engineer
KenCast Inc.