Need help with an update statment

[jayjabour]

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

[PAVB]

>> 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.

[jayjabour]

how can I re-write the sript to update just the rows returned from the select statement?

[hrishy]

Hi

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

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

[LKBrwn_DBA]

Maybe this will work better:

 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)
  );

[jayjabour]

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.

[jayjabour]

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?

[hrishy]

Hi

How about

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
);