Home » SQL & PL/SQL » SQL & PL/SQL » update query required (oracle 10g)
update query required [message #436531] Thu, 24 December 2009 00:52 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,

this is my query.

UPDATE EMRPatientAdmitComanager SET DISCHARGE_APPROVED= (SELECT PAC.DISCHARGE_APPROVED FROM
EMRPatientAdmitComanager PAC INNER JOIN EMRPatientAdmitInfo PAI ON PAC.ADMIT_ID = PAI.ADMIT_ID WHERE PAI.PATIENT_ID='91983'
AND PAC.USER_ID = '1507_c' --AND PAC.DISCHARGE_APPROVED=1)

here i want to update DISCHARGE_APPROVED=1 when that condition and patient_id is there.but how to update it to 1.
Re: update query required [message #436532 is a reply to message #436531] Thu, 24 December 2009 01:05 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
...DISCHARGE_APPROVED= (SELECT 1 FROM...


regards,
Delna
Re: update query required [message #436533 is a reply to message #436531] Thu, 24 December 2009 01:05 Go to previous messageGo to next message
soni_7
Messages: 33
Registered: July 2005
Member
Hi,

You can try using decode clause in the update statement. If the count of your condition gives 1 then set to 1.

Regards
Soni
Re: update query required [message #436546 is a reply to message #436531] Thu, 24 December 2009 02:07 Go to previous message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
Just some notes to your code. As the subquery in the SET clause contains no reference to updated table, it returns the same resultset for all updated rows. Most probably, it will be more than one row, which will cause ORA-01427 "single-row subquery returns more than one row" exception. Also, as it contains no WHERE clause, it will update all rows in EMRPatientAdmitComanager (if it succeeds).

If you want to update rows to 1, just use 1 in the SET clause.
If you want to update only selected rows, just add appropriate condition into WHERE clause.
UPDATE EMRPatientAdmitComanager PAC
SET DISCHARGE_APPROVED = 1
WHERE PAC.USER_ID = '1507_c'
  AND EXISTS( SELECT 1
              FROM EMRPatientAdmitInfo PAI 
              WHERE PAC.ADMIT_ID = PAI.ADMIT_ID
                AND PAI.PATIENT_ID='91983');

Note, that I removed the reference to EMRPatientAdmitComanager from the subquery, so now it refers to the values in the row which is updated.

[Edit: typos]

[Updated on: Thu, 24 December 2009 02:10]

Report message to a moderator

Previous Topic: backup and restore the view
Next Topic: ORA-12015: cannot create a fast refresh materialized view ..
Goto Forum:
  


Current Time: Sun Sep 25 04:25:09 CDT 2016

Total time taken to generate the page: 0.23770 seconds