-
help with the where clause of an update
I have a table called PITEm. it has several rows and data something like this:
pateint fitem date
1234 123 11-JUN-2010
1234 123 14-MAY-2010
1234 456 11-JUN-2010
1234 456 14-MAY-2010
1234 789 01-JUL-2009
1234 789 14-MAY-2010
I want to update the date for each fitem but only the most recent date. I wrote this select query:
select "FItem", max("Date") from PITEM where "Patient"='711613' and "FormCode"='AMEDHX' group by "FItem" order by "FItem"
it gives me this (which is what I want):
1234 123 11-JUN-2010
1234 456 11-JUN-2010
1234 789 14-MAY-2010
this gave me the most recent date for each fitem for this patient. So I want to update these rows. I am stumped on how to update this. Here is what I got so far:
update PITEM set "Date"=(select trunc(sysdate) from dual) where "Patient"='711613' and "FormCode"='AMEDHX' and "FItem"= (select Distinct "FItem" from PITEM where "Patient"='711613' and "FormCode"='AMEDHX') and "Date" in (select "FItem", max("Date") from PITEM where "Patient"='711613' and "FormCode"='AMEDHX' group by "FItem")
this returns an error of too many values. I am just not sure how to re-write this.
-
typo
Something like...
Code:
update pitem a
set a.date = sysdate
where exists = (select b.pateint,b.fitem,max(b.date)
from pitem b
where a.pateint = b.pateint
and a.fitem = b.fitem
and a.date = max(b.date)
group by b.pateint,b.fitem)
;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks for the reply, sorry I didn't get back to you sooner. I tried this and am getting an error missing left parenthesis.
After some research on the exists clause I changed it to
update pitem a
set a."Date" = sysdate
where exists ( select b."Pateint", b."Fitem", max(b."Date")
from pitem b
where a."Pateint" = '711613'
and a."Pateint" = b."Pateint"
and a."Fitem" = b."Fitem"
and a."Date" = max(b."Date")
group by b."Pateint", b."Fitem")
removing the = after the exists now I am getting this error:
ORA-00934: group function is not allowed here
looks like it doesn't like :
and a."Date" = max(b."Date")
Last edited by jayjabour; 07-26-2010 at 08:59 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|