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

Threaded View

  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 05:13 PM. Reason: spelling

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