Need help with an update statment
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Need help with an update statment

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    Need help with an update statment

    I need to write a script that updates rows in one table based on a select statement I was given. Here is the script I was given

    SELECT "APPOINT"."Clinic", "APPOINT"."Type", "TRX"."Discipline"
    FROM APPOINT, TRX
    WHERE ((APPOINT."Date"=TRX."TreatmentDate") AND (APPOINT."Patient"=TRX."Patient")) AND APPOINT."Clinic"=1320 AND (APPOINT."Type"=0 OR APPOINT."Type"=1 OR APPOINT."Type"=2)

    this returns about 300 rows.

    I originally wrote this update script

    update TRX set "Discipline"='SBP' where TRX."Discipline"= (SELECT "APPOINT"."Clinic", "APPOINT"."Type", "TRX"."Discipline"
    FROM "APPOINT", "TRX"
    WHERE (("APPOINT"."Date"="TRX"."TreatmentDate") AND ("APPOINT"."Patient"="TRX"."Patient")) AND "APPOINT"."Clinic"=1320 AND ("APPOINT"."Type"=0 OR "APPOINT"."Type"=1 OR "APPOINT"."Type"=2))

    but got an error ORA-00913: too many values. I understand now why I got that that so I tried this:

    update TRX set "TRX.Discipline"='SBP' WHERE APPOINT."Date"=TRX."TreatmentDate" AND APPOINT."Patient"=TRX."Patient" AND APPOINT."Clinic"=1320 AND APPOINT."Type"=0 OR APPOINT."Type"=1 OR APPOINT."Type"=2

    This one gives me this error
    ORA-00904: "APPOINT"."Type": invalid identifier

    Type is a valid field in the appoint table. I think I am getting this because the appoint table is not in the begining of the sql. Can anyone please give me some insight as to what I am doing wrong?? thank You
    Last edited by jayjabour; 01-15-2010 at 04:13 PM. Reason: spelling

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    >> ORA-00913
    This is because of inline view a.k.a. subquery is returning three values APPOINT.Clinic, APPOINT.Type and APPOINT.Discipline which script attempts to compare with just one value, TRX.Discipline.


    >> ORA-00904
    This is because update statement attempts to update table TRX but is referencing columns that belong to a different non-referenced table, in this case APPOINT.
    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
    how can I re-write the sript to update just the rows returned from the select statement?

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    You might want to verify if this is what you require.

    Code:
    UPDATE trx
    set TRX.Discipline = (
       SELECT 'SBP'
       FROM APPOINT
       WHERE APPOINT.Date = TRX.TreatmentDate 
       AND APPOINT.Patient = TRX.Patient
       AND APPOINT.Clinic = 1320 
       AND APPOINT.Type = 0 
       OR APPOINT.Type = 1 
       OR APPOINT.Type = 2)
    http://www.morganslibrary.org/reference/update.html
    Last edited by hrishy; 01-17-2010 at 11:51 PM.

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,476

    Cool Does it exists?

    Maybe this will work better:
    Code:
     UPDATE TRX
        SET Discipline='SBP'
      WHERE EXISTS   
      (SELECT '?'
         FROM APPOINT
        WHERE APPOINT.Date=TRX.TreatmentDate
          AND APPOINT.Patient=TRX.Patient
          AND APPOINT.Clinic  =1320
          AND APPOINT.Type  IN (0,1,2)
      );
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  6. #6
    Join Date
    Mar 2005
    Posts
    143
    LKBrwn_DBA,
    thank you however when I run your update it updates 2 million rows, which conuses me becase is I just run the SELECT '?'
    FROM APPOINT
    WHERE APPOINT.Date=TRX.TreatmentDate
    AND APPOINT.Patient=TRX.Patient
    AND APPOINT.Clinic =1320
    AND APPOINT.Type IN (0,1,2)
    it only returns 377 rows.

  7. #7
    Join Date
    Mar 2005
    Posts
    143
    OK, I moddified the SQL a little am an getting closer. this is what I am using

    UPDATE TRX
    SET "Discipline"='SBP'
    WHERE "Id" IN
    (SELECT "Id"
    FROM APPOINT, TRX
    WHERE APPOINT."Date"=TRX."TreatmentDate"
    AND APPOINT."Patient"=TRX."Patient"
    AND APPOINT."Clinic" =1320
    AND APPOINT."Type" IN (0,1,2)
    );

    this update 378 rows. However the select by itself anly grabs 377 rows. I am not sure why it is updating one extra row. Any advice on that?

  8. #8
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    How about

    Code:
    UPDATE TRX
    SET Discipline='SBP'
    WHERE EXISTS
    (SELECT 'X'
    FROM APPOINT, TRX
    WHERE APPOINT."Date"=TRX."TreatmentDate"
    AND APPOINT."Patient"=TRX."Patient"
    AND APPOINT."Clinic" =1320
    AND APPOINT."Type" IN (0,1,2)
    AND APPOINT.ID = TRX.ID
    );

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