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