Home » SQL & PL/SQL » SQL & PL/SQL » query required (oracle 10g)
query required [message #397893] Tue, 14 April 2009 07:59 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
i am having query like this
UPDATE EMRIDS SET EZEMRXID = (SELECT MAX(PARENT_LAB_REQUEST_ID) FROM EMRParentLabRequestMaster) WHERE PROPERTY_NAME = 'PARENT_LAB_REQUEST_ID'
/

i am not having any records in EMRParentLabRequestMaster table.
so i am facing exception like this

Error starting at line 1,017 in command:
UPDATE EMRIDS SET EZEMRXID = (SELECT MAX(PARENT_LAB_REQUEST_ID) FROM EMRParentLabRequestMaster) WHERE PROPERTY_NAME = 'PARENT_LAB_REQUEST_ID'
Error report:
SQL Error: ORA-01407: cannot update ("TESTINGDATA"."EMRIDS"."EZEMRXID") to NULL
01407. 00000 -  "cannot update (%s) to NULL"
*Cause:    
*Action:

so i want some conditions for the above query without producing any exception.can we do it without any error?


Re: query required [message #397895 is a reply to message #397893] Tue, 14 April 2009 08:03 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
You need a correlated subquery to add the condition that the value must exist in the second table. That was a hint.
Re: query required [message #397897 is a reply to message #397893] Tue, 14 April 2009 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
can we do it without any error?

Do not try to update a NOT NULL field with NULL.

Use SQL Formatter and keep your lines in 80 characters.

Regards
Michel
Re: query required [message #397899 is a reply to message #397897] Tue, 14 April 2009 08:07 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
how to write a correlated sub query for this?
can't we directly do it?
Re: query required [message #397901 is a reply to message #397899] Tue, 14 April 2009 08:10 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Do you know what a correlated subquery is? If not, look it up in Google. I pretty much gave you the full answer in my hint. You need some problem solving ability to do it. If you do not have that ability, then programming is the wrong career path for you.
Re: query required [message #397904 is a reply to message #397893] Tue, 14 April 2009 08:20 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually, if OP really just wants the max(PARENT_LAB_REQUEST_ID) of all the rows in EMRParentLabRequestMaster
(there's no where on the select) then a simple nvl will suffice.

Or you could just make sure there's actually some data in that table before you do anything else.
Re: query required [message #397907 is a reply to message #397904] Tue, 14 April 2009 08:27 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
UPDATE EMRIDS SET EZEMRXID =
COALESCE ((SELECT MAX(PARENT_LAB_REQUEST_ID) FROM EMRParentLabRequestMaster),0)
WHERE PROPERTY_NAME = 'PARENT_LAB_REQUEST_ID'
/


HELLO joy_division,
go and learn oracle once again ok
Re: query required [message #397923 is a reply to message #397907] Tue, 14 April 2009 09:54 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First of all, a bit of modesty might be in order, since YOU are the one who asked a question. joy_division took some of his FREE time to help you.
Secondly, how on earth could anyone but you know that you want a value of zero for the cases where no max could be found?
Previous Topic: SQL query
Next Topic: Group by help (merged)
Goto Forum:
  


Current Time: Sun Dec 11 02:36:20 CST 2016

Total time taken to generate the page: 0.09295 seconds