Re: Heres a good one...

I just wanted to show the possibility of trying your problem using 

1. Update using another table

2. Use of subselects

I forgot to add parantheses around the two subselect statements. The
following should be okey.

update journal set gl_update_flag='Y' where gl_update_flag = 'H' and
posting_date <= '2001-03-31' and ((objectid in (select distrib_objectid
from distrib)) or (objectid in (select source_objectid from distrib)))

Can you comment on the speed of this. I sometimes found it to be slow.

Also, can anybody tell me if the two statements can be redeced to a single
"canonical" form?

Anuradha

On Mon, 30 Apr 2001, Steve Meynell wrote:

> First,  thank you very much for trying but you were a bit off the mark but
> close.  Second, posting_date being of type char(4) is quite legal and
> irrelevant for this exercise but for argument sake lets say char(15) but
> apart from all that...
> 
> Your sql statement was close but it only update 4 out of the possible 6 that
> is should have updated...  journal.objectid# 103 should have been updated
> because of journal.objectid# 100 was and journal.objectid# 106 should have
> been because it alone met the before March 31 and update flag = H criteria.
> But it is a start for me thanks.
> 
> And your second sql statement just didn't work.  It said 'ERROR:  parser:
> parse error at or near "select"'
> 
> Steve
> 
> 
> Anuradha Ratnaweera wrote:
> 
> > First, posting_date in journal can _NOT_ be of type char(4)! I guess it is
> > a "date".
> >
> > Try
> >
> > update journal set gl_update_flag='Y' from distrib where
> > journal.gl_update_flag = 'H' and journal.posting_date <= '2001-03-31' and
> > (journal.objectid = distrib.distrib_objectid or journal.objectid =
> > distrib.source_objectid)
> >
> > or
> >
> > update journal set gl_update_flag='Y' where gl_update_flag = 'H' and
> > posting_date <= '2001-03-31' and ((objectid in select distrib_objectid
> > from distrib) or (objectid in select source_objectid from distrib))
> >
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
>