-
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
-
>> 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.
-
how can I re-write the sript to update just the rows returned from the select statement?
-
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-18-2010 at 12:51 AM.
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|