DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2005

    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.

  2. #2
    Join Date
    Mar 2007
    Ft. Lauderdale, FL


    Something like...
    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.

  3. #3
    Join Date
    Mar 2005
    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

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.