help with the where clause of an update
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: help with the where clause of an update

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    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
    Location
    Ft. Lauderdale, FL
    Posts
    3,554

    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.

  3. #3
    Join Date
    Mar 2005
    Posts
    143
    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 09: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