pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquestion on UPDATE TABLE
3+ messages / 2 participants
[nested] [flat]
* question on UPDATE TABLE
@ 2002-08-23 19:16 Wei Weng <wweng@kencast.com>
2002-08-23 19:51 ` Re: question on UPDATE TABLE Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: Wei Weng @ 2002-08-23 19:16 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: question on UPDATE TABLE
2002-08-23 19:16 question on UPDATE TABLE Wei Weng <wweng@kencast.com>
@ 2002-08-23 19:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-23 19:56 ` Re: question on UPDATE TABLE Wei Weng <wweng@kencast.com>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2002-08-23 19:51 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: question on UPDATE TABLE
2002-08-23 19:16 question on UPDATE TABLE Wei Weng <wweng@kencast.com>
2002-08-23 19:51 ` Re: question on UPDATE TABLE Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2002-08-23 19:56 ` Wei Weng <wweng@kencast.com>
0 siblings, 0 replies; 3+ messages in thread
From: Wei Weng @ 2002-08-23 19:56 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-08-23 19:56 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-23 19:16 question on UPDATE TABLE Wei Weng <wweng@kencast.com>
2002-08-23 19:51 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-08-23 19:56 ` Wei Weng <wweng@kencast.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox