Re: updating with subquery

GS schrieb:
> I have done this before, but it's been awhile and I am stumped and am
> missing something simple here. I have columns in table a that need a num
> value changed from a 4 to a 3 where they are 4 and also meet a join
> condition in another table. First I make a query that returns the
> correct rows:
> =====================================================================
> select approvalstat
> from a.project ps, a.projectstatus p
> where p.projectstatus = ps.projstatus
> and p.projctstatus = 'Project in Progress' and approvalstat = 4
>
> SQL> 521 rows returned
> =====================================================================
>
> so then I try:
>
> =====================================================================
> update a.project set approvalstat = 3
> where approvalstat in
> (
> select approvalstat
> from a.project ps, a.projectstatus p
> where p.projectstatus = ps.projstatus
> and p.projctstatus = 'Project in Progress' and approvalstat = 4
> )
> =====================================================================
>
> and I get 823 rows updated, so I try:
>
> =====================================================================
> update a.project set approvalstat = 3
> where exists
> (
> select approvalstat
> from a.project ps, a.projectstatus p
> where p.projectstatus = ps.projstatus
> and p.projctstatus = 'Project in Progress' and approvalstat = 4
> )
> =====================================================================
>
> and again get incorrect # of rows updated.
>
> What am I missing in my syntax?
>
> thanks

Your subqueries are not tied to outer query, so you are losing the context of the join.
This may be one approach:

update a.project p set approvalstat = 3
where approvalstat=4
and exists
(
  select null
  from a.projectstatus ps
  where p.projectstatus = ps.projectstatus   and ps.projctstatus = 'Project in Progress' )

If however you have a typo (sorry for that, but it looks very suspicious ;-)) and *projctstatus* is indeed the same as *projectstatus*, then even *exists* is not needed, just one filter more.

Best regards

Maxim Received on Thu Jan 24 2008 - 16:55:31 CST