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>

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