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.