update multiple rows [message #354227] |
Fri, 17 October 2008 01:59  |
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 #354236 is a reply to message #354228] |
Fri, 17 October 2008 02:37   |
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   |
flyboy
Messages: 1903 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 #354272 is a reply to message #354236] |
Fri, 17 October 2008 05:20  |
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
|
|
|