Home » SQL & PL/SQL » SQL & PL/SQL » How to see/check the null records for this update statement; getting ORA-01407 error ? (oracle 9.2.0.1)
How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311591] |
Fri, 04 April 2008 21:47  |
gksenthilkumar
Messages: 23 Registered: November 2007 Location: india
|
Junior Member |
|
|
How to see/check the null records for this update statement; getting ORA-01407 error ?
I have tried but not able to get / am not sure doing right way...could any one please help..
ORA-01407: cannot update ("MIMI"."MON_PLAN"."PLAN_YTD_01") to NULL
UPDATE /*+ INDEX(M MON_PLAN_IDX_PK)
NOPARALLEL(M) NOPARALLEL_INDEX(M MON_PLAN_IDX_PK) */
MON_PLAN M
SET (plan_ytd_01,
plan_ytd_02,
plan_ytd_03,
plan_ytd_04,
plan_ytd_05,
plan_ytd_06,
plan_ytd_07,
plan_ytd_08,
plan_ytd_09,
plan_ytd_10,
plan_ytd_11,
plan_ytd_12) =
(SELECT /*+ ORDERED INDEX(T MON_PLAN_IDX_PK) USE_NL (T)
INDEX(C COA_REF_M_IDX_PK) USE_NL(C)
NOPARALLEL(T) NOPARALLEL_INDEX(T MON_PLAN_IDX_PK) */
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 ) ) ,2),
(M.plan_mtd_01 )), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1)))
/ (1 +59) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02)), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31))
/ (1 +90) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03)), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30))
/ (1 +120) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04)), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31))
/ (1 +151) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05)), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30))
/ (1 +181) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06)), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31))
/ (1 +212) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07)), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31)
+ (M.plan_mtd_08 * 31))
/ (1 +243) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07
+ M.plan_mtd_08)), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31)
+ (M.plan_mtd_08 * 31)
+ (M.plan_mtd_09 * 30))
/ (1 +273) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07
+ M.plan_mtd_08
+ M.plan_mtd_09)), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31)
+ (M.plan_mtd_08 * 31)
+ (M.plan_mtd_09 * 30)
+ (M.plan_mtd_10 * 31))
/ (1 +304) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07
+ M.plan_mtd_08
+ M.plan_mtd_09
+ M.plan_mtd_10)), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31)
+ (M.plan_mtd_08 * 31)
+ (M.plan_mtd_09 * 30)
+ (M.plan_mtd_10 * 31)
+ (M.plan_mtd_11 * 30))
/ (1 +334) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07
+ M.plan_mtd_08
+ M.plan_mtd_09
+ M.plan_mtd_10
+ M.plan_mtd_11)), 0)
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31)
+ (M.plan_mtd_08 * 31)
+ (M.plan_mtd_09 * 30)
+ (M.plan_mtd_10 * 31)
+ (M.plan_mtd_11 * 30)
+ (M.plan_mtd_12 * 31))
/ (1 +365) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07
+ M.plan_mtd_08
+ M.plan_mtd_09
+ M.plan_mtd_10
+ M.plan_mtd_11
+ M.plan_mtd_12)), 0)
FROM MON_PLAN T, COA_REF_M C
WHERE
T.REC_TYPE = M.REC_TYPE
AND T.ENT = M.ENT
AND T.SUB_ENT = M.SUB_ENT
AND T.POST_ACCT = M.POST_ACCT
AND T.CTR_OR_HIER = M.CTR_OR_HIER
AND T.YR = M.YR
AND T.PLAN_TYPE = M.PLAN_TYPE
AND T.SUM_FLAG = M.SUM_FLAG
AND T.ADJ = M.ADJ
AND T.PRODUCT_CODE = M.PRODUCT_CODE
AND T.SEGMENT_CODE = M.SEGMENT_CODE
AND T.POST_ACCT = C.ACCT
AND C.ACCT_LVL = '8'
AND C.ACCT_ID = 'primary'
AND C.REL_TYPE = ' ')
WHERE
M.REC_TYPE = 'A'
AND M.ENT = '9'
AND M.SUB_ENT = '0'
AND M.POST_ACCT like '8%'
AND M.CTR_OR_HIER like '%'
AND M.YR = '2008'
AND M.PLAN_TYPE = 'RAMBO'
AND M.SUM_FLAG LIKE '%'
AND M.ADJ >= ' '
AND M.PRODUCT_CODE = 'ALL'
AND M.SEGMENT_CODE = 'ALL';
|
|
|
|
Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311615 is a reply to message #311591] |
Sat, 05 April 2008 02:00   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Maybe a better alternative would be this: as you issue NVL on all columns in the subquery, the NULL rows are caused by non-existence of corresponding row in T and C tables. It is also specified in http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2073065.
The solution: copy your subquery into the WHERE condition (you may replace the SELECT columns with anything, eg. 1): UPDATE MON_PLAN M
SET (<column list>) = (<subquery>)
WHERE <current conditions on M>
AND EXISTS( SELECT 1
FROM MON_PLAN T, COA_REF_M C
WHERE <conditions from the subquery> )
If you want to update rows without join to T and C tables with NULLs (zeros or whatever), then issue another UPDATE statement like this: UPDATE MON_PLAN M
SET (<column list>) = (<list of NULLs, zeros, etc.>)
WHERE <current conditions on M>
AND NOT EXISTS( SELECT 1
FROM MON_PLAN T, COA_REF_M C
WHERE <conditions from the subquery> )
|
|
|
|
|
|
|
Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311691 is a reply to message #311689] |
Sat, 05 April 2008 14:34   |
gksenthilkumar
Messages: 23 Registered: November 2007 Location: india
|
Junior Member |
|
|
I have tried using that update error tracing clause its getting error
ERROR at line 238:
ORA-00904: "PM01": invalid identifier
15:31:02 SQL>
var pmm01 number;
set serveroutput on;
UPDATE /*+ INDEX(M MON_PLAN_IDX_PK)
NOPARALLEL(M) NOPARALLEL_INDEX(M MON_PLAN_IDX_PK) */
MON_PLAN M
SET (plan_ytd_01,
plan_ytd_02,
plan_ytd_03,
plan_ytd_04,
plan_ytd_05,
plan_ytd_06,
plan_ytd_07,
plan_ytd_08,
plan_ytd_09,
plan_ytd_10,
plan_ytd_11,
plan_ytd_12) =
(SELECT /*+ ORDERED INDEX(T MON_PLAN_IDX_PK) USE_NL (T)
INDEX(C COA_REF_M_IDX_PK) USE_NL(C)
NOPARALLEL(T) NOPARALLEL_INDEX(T MON_PLAN_IDX_PK) */
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 ) ) ,2),
(M.plan_mtd_01 )), 0) pm01
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1)))
/ (1 +59) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02)), 0) pm02
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31))
/ (1 +90) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03)), 0) pm03
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30))
/ (1 +120) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04)), 0) pm04
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31))
/ (1 +151) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05)), 0) pm05
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30))
/ (1 +181) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06)), 0) pm06
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31))
/ (1 +212) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07)), 0) pm07
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31)
+ (M.plan_mtd_08 * 31))
/ (1 +243) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07
+ M.plan_mtd_08)), 0) pm08
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31)
+ (M.plan_mtd_08 * 31)
+ (M.plan_mtd_09 * 30))
/ (1 +273) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07
+ M.plan_mtd_08
+ M.plan_mtd_09)), 0) pm09
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31)
+ (M.plan_mtd_08 * 31)
+ (M.plan_mtd_09 * 30)
+ (M.plan_mtd_10 * 31))
/ (1 +304) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07
+ M.plan_mtd_08
+ M.plan_mtd_09
+ M.plan_mtd_10)), 0) pm10
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31)
+ (M.plan_mtd_08 * 31)
+ (M.plan_mtd_09 * 30)
+ (M.plan_mtd_10 * 31)
+ (M.plan_mtd_11 * 30))
/ (1 +334) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07
+ M.plan_mtd_08
+ M.plan_mtd_09
+ M.plan_mtd_10
+ M.plan_mtd_11)), 0) pm11
,
NVL(DECODE(DECODE(c.acct_type,'01', 'B', '05', 'B', '10', 'B', '90', 'B','I'),
'B', ROUND(((M.plan_mtd_01 * 31)
+ (M.plan_mtd_02 * (28 + 1))
+ (M.plan_mtd_03 * 31)
+ (M.plan_mtd_04 * 30)
+ (M.plan_mtd_05 * 31)
+ (M.plan_mtd_06 * 30)
+ (M.plan_mtd_07 * 31)
+ (M.plan_mtd_08 * 31)
+ (M.plan_mtd_09 * 30)
+ (M.plan_mtd_10 * 31)
+ (M.plan_mtd_11 * 30)
+ (M.plan_mtd_12 * 31))
/ (1 +365) ,2),
(M.plan_mtd_01
+ M.plan_mtd_02
+ M.plan_mtd_03
+ M.plan_mtd_04
+ M.plan_mtd_05
+ M.plan_mtd_06
+ M.plan_mtd_07
+ M.plan_mtd_08
+ M.plan_mtd_09
+ M.plan_mtd_10
+ M.plan_mtd_11
+ M.plan_mtd_12)), 0) pm12
FROM MON_PLAN T, COA_REF_M C
WHERE
T.REC_TYPE = M.REC_TYPE
AND T.ENT = M.ENT
AND T.SUB_ENT = M.SUB_ENT
AND T.POST_ACCT = M.POST_ACCT
AND T.CTR_OR_HIER = M.CTR_OR_HIER
AND T.YR = M.YR
AND T.PLAN_TYPE = M.PLAN_TYPE
AND T.SUM_FLAG = M.SUM_FLAG
AND T.ADJ = M.ADJ
AND T.PRODUCT_CODE = M.PRODUCT_CODE
AND T.SEGMENT_CODE = M.SEGMENT_CODE
AND T.POST_ACCT = C.ACCT
AND C.ACCT_LVL = '8'
AND C.ACCT_ID = 'primary'
AND C.REL_TYPE = ' ')
WHERE
M.REC_TYPE = 'A'
AND M.ENT like '910'
AND M.SUB_ENT = '000'
AND M.POST_ACCT like '8%'
AND M.CTR_OR_HIER like '%'
AND M.YR = '2008'
AND M.PLAN_TYPE like 'ztest'
AND M.SUM_FLAG LIKE '%'
AND M.ADJ >= ' '
AND M.PRODUCT_CODE = 'ALL'
AND M.SEGMENT_CODE = 'ALL'
RETURNING pm01 into pmm01
|
|
|
|
|
|
|
Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311768 is a reply to message #311695] |
Sun, 06 April 2008 13:01   |
gksenthilkumar
Messages: 23 Registered: November 2007 Location: india
|
Junior Member |
|
|
Here is the output fetched from sql session for the statement
still its giving the same error instead of locating specific point...
SQL> @UPDATE_TEST_SQL.sql;
UPDATE /*+ INDEX(M MON_PLAN_IDX_PK)
*
ERROR at line 1:
ORA-01407: cannot update ("INSIGHT"."MON_PLAN"."PLAN_YTD_01") to NULL
M_REC_TYPE
--------------------------------
M_ENT
--------------------------------
M_SUB_ENT
--------------------------------
M_POST_ACCT
--------------------------------
M_CTR_OR_HIER
--------------------------------
M_YR
--------------------------------
M_PLAN_TYPE
--------------------------------
M_SUM_FLAG
--------------------------------
M_ADJ
--------------------------------
M_PRODUCT_CODE
--------------------------------
M_SEGMENT_CODE
--------------------------------
SQL>
|
|
|
|
Re: How to see/check the null records for this update statement; getting ORA-01407 error ? [message #311835 is a reply to message #311591] |
Mon, 07 April 2008 01:40  |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
from email | Could you please help me, here I want issue the update same functionality without nulls;
You given three approch, but I do not know which one is sutable for my case, could you please advice me the exact one plz..
|
Just a slight correction: I gave only two approaches: correlated subquery (you use, both queries in my first post) or correlated update (query my second post).
There are two possible scenarios of your update:
S1) update all rows in M with <current conditions on M>
S2) restrict S1 scope only to the rows which have representation in T and C tables
I do not know, which is your case; but they do not differ a lot, so I described both.
From my point of view, correlated update is better; you may even display the results before UPDATE. In case you want to follow scenario S1, you just need to use OUTER JOIN instead of INNER JOIN. See details in http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/queries7.htm#2054014.
[Edit: typo]
[Updated on: Mon, 07 April 2008 01:40] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Sat Aug 09 19:00:32 CDT 2025
|