Home » SQL & PL/SQL » SQL & PL/SQL » update multiple rows (oracle 9i)
update multiple rows [message #354227] Fri, 17 October 2008 01:59 Go to next message
mohan404
Messages: 2
Registered: October 2008
Junior Member
hi all,

actually iam trying to update table 1 with values from table 2 in this time am encountering one error ora-01407.and in another situation the select statement from table2 is returning multiple rows in this situation i want update multiple rows in table 1.Please any body help in this regard.
Re: update multiple rows [message #354228 is a reply to message #354227] Fri, 17 October 2008 02:09 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
ora-01407: which means you cannot update the column to NULL when it does not accept NULL values.


Can you please post what you have done?



Regards,
Oli

[Updated on: Fri, 17 October 2008 02:10]

Report message to a moderator

Re: update multiple rows [message #354236 is a reply to message #354228] Fri, 17 October 2008 02:37 Go to previous messageGo to next message
mohan404
Messages: 2
Registered: October 2008
Junior Member
T_EMPLOYEE-- MAIN TABLE HAVE PRIMARY KEY EMPLOYEE_NO,EMPLOYEE_ID
T_EMPLOYEE_BALANCE --EMPLOYEE_ID(FK)
T_EMP_STAGING --EMPLOYEE_NO(FK)

AND I WANT TO UPDATE THE TABLE T_EMPLOYEE_BALANCE WITH RESPECT TO T_EMP_STAGING TABLE
A
ND THE QUERY IS....

UPDATE T_EMPLOYEE_BALANCE SET (EMPLOYEE_ID,SCHEME_ID,YEAR_BEGIN_DATE,YEAR_END_DATE,BALANCE,CREATED_BY,CREATED_DT,MODIFIED_BY,MODIFIED_DATE,ENTITLEMENT)=(
SELECT EMP.EMPLOYEE_ID, STAGBAL.SCHEME_ID,STAGBAL.YEAR_BEGIN_DATE, STAGBAL.YEAR_END_DATE
, STAGBAL.BALANCE, STAGBAL.CREATED_BY, STAGBAL.CREATED_DT, STAGBAL.MODIFIED_BY, STAGBAL.MODIFIED_DATE
,STAGBAL.ENTITLEMENT
FROM T_EMP_STAGING STAGBAL, T_EMPLOYEE EMP WHERE
EMP.EMPLOYEE_NO=STAGBAL.EMPLOYEE_NO AND STAGBAL.SCHEME_ID =4 AND STAGBAL.READ_FLAG='N')
Re: update multiple rows [message #354251 is a reply to message #354227] Fri, 17 October 2008 03:33 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Firstly, stop shouting. Definitely.
Secondly, if Olivia's response was not satisfatory, simple description of the error shall give you more insight:
http://ora-01407.ora-code.com/
Quote:
ORA-01407: cannot update (string) to NULL
Cause: An attempt was made to update a table column "USER"."TABLE"."COLUMN" with a NULL value.

For example, if you enter:

connect scott/tiger update table a (a1 number not null); insert into a values (null);

Oracle returns:

ORA-01407 cannot update ("SCOTT"."A"."A1") to NULL

which means you cannot update the column "SCOTT"."A"."A1" to NULL.
Action: Retry the operation with a value other than NULL.
Re: update multiple rows [message #354268 is a reply to message #354227] Fri, 17 October 2008 04:46 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



Quote:
and in another situation the select statement from table2 is returning multiple rows in this situation i want update multiple rows in table 1


Make sure that Subquery used in update statement returns only One value by using appropriate where clauses

Thumbs Up
Rajuvan.
Re: update multiple rows [message #354272 is a reply to message #354236] Fri, 17 October 2008 05:20 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
By the way I am not sure you realised or not you are updating all the rows in the source table with a value returned from the subquery or it is getting updated with null. I am not sure this is what you want to do.

Regards

Raj

P.S : Spend some in reading the forum guidelines
Previous Topic: query
Next Topic: "ORA-12519: TNS:no appropriate service handler found"
Goto Forum:
  


Current Time: Fri Dec 02 20:43:34 CST 2016

Total time taken to generate the page: 0.25482 seconds