Home » SQL & PL/SQL » SQL & PL/SQL » update query failing (oracle 10g)
update query failing [message #411929] Tue, 07 July 2009 05:38 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi my update query is fauling it is like this
UPDATE EMRAppointmentDetails
SET TP_PKG_ID = b.TP_PKG_ID
FROM EMRAppointmentDetails a,
  (SELECT EAH.TP_PKG_ID,
    EA.APPOINTMENT_ID
  FROM EMRAppointmentDetails EA
  INNER JOIN EMRAppointmentDetailsHistory EAH
  ON EA.APPOINTMENT_ID           =EAH.APPOINTMENT_ID
  WHERE EA.aPPOINTMENT_STATUS_ID =3
  AND EAH.aPPOINTMENT_STATUS_ID  = 8
  AND EAH.TP_PKG_ID             IS NOT NULL
  AND EA.TP_PKG_ID              IS NULL
  AND EA.HEALTH_PKG_ID          IS NOT NULL
  ) b
WHERE b.APPOINTMENT_ID = a.APPOINTMENT_ID
/

Error at Command Line:2 Column:27
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
Re: update query failing [message #411931 is a reply to message #411929] Tue, 07 July 2009 05:45 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
From isn't valid in an update statement. Suggest you read up on updates.
Re: update query failing [message #411934 is a reply to message #411931] Tue, 07 July 2009 05:47 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
actually mine is an mssql query iam unable to find it in oracle?
Re: update query failing [message #411936 is a reply to message #411934] Tue, 07 July 2009 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what did you try?

Regards
Michel
Re: update query failing [message #411937 is a reply to message #411929] Tue, 07 July 2009 05:51 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're converting mssql code to oracle then you need to learn oracle syntax. The documentation would be a good place to start:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#sthref9987
Re: update query failing [message #411941 is a reply to message #411937] Tue, 07 July 2009 05:54 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
can you help me out in giving the query in oracle
Re: update query failing [message #411947 is a reply to message #411941] Tue, 07 July 2009 05:59 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You should see my reply in one of your previous post that will give you some starters.

http://www.orafaq.com/forum/t/147279/73596/

[Updated on: Tue, 07 July 2009 05:59]

Report message to a moderator

Re: update query failing [message #411951 is a reply to message #411941] Tue, 07 July 2009 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rajasekhar857 wrote on Tue, 07 July 2009 12:54
can you help me out in giving the query in oracle

update ... set ... = (select ...)

Regards
Michel
Re: update query failing [message #411982 is a reply to message #411951] Tue, 07 July 2009 06:42 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yeah i got it.thanks for that

Update EMRAppointmentDetails a
SET a.TP_PKG_ID = (select b.TP_PKG_ID from
(select EAH.TP_PKG_ID,EA.APPOINTMENT_ID from EMRAppointmentDetails EA
inner join EMRAppointmentDetailsHistory EAH on EA.APPOINTMENT_ID=EAH.APPOINTMENT_ID
where EA.aPPOINTMENT_STATUS_ID =3 and EAH.aPPOINTMENT_STATUS_ID = 8
and EAH.TP_PKG_ID is not null and EA.TP_PKG_ID is null and EA.HEALTH_PKG_ID is not null) b
Where b.APPOINTMENT_ID = a.APPOINTMENT_ID)
/

this is the way i have done .its working fine
Re: update query failing [message #411986 is a reply to message #411982] Tue, 07 July 2009 06:46 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please FORMAT your queries and use CODE tags.

Regards
Michel
Previous Topic: DBMS_OUTPUT on UTL_FILE
Next Topic: Parse
Goto Forum:
  


Current Time: Sun Dec 11 02:23:06 CST 2016

Total time taken to generate the page: 0.22316 seconds