updating with subquery
From: GS <GS_at_GS.com>
Date: Thu, 24 Jan 2008 22:22:17 GMT
Message-ID: <tc8mj.14148$vp3.13516@edtnps90>
select approvalstat
from a.project ps, a.projectstatus p
where p.projectstatus = ps.projstatus
and p.projctstatus = 'Project in Progress' and approvalstat = 4
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 )
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 )
Date: Thu, 24 Jan 2008 22:22:17 GMT
Message-ID: <tc8mj.14148$vp3.13516@edtnps90>
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 Received on Thu Jan 24 2008 - 16:22:17 CST