| help me please [message #41283] |
Fri, 13 December 2002 16:10  |
Vinod
Messages: 76
Registered: April 1999
|
Member |
|
|
I have a query result as follows
select a.sector_code OLD,b.new_sector_code NEW
from sector_lookup_old b,procurement_action_sector a
where a.sector_code=b.sector_code
group by a.sector_code,b.new_sector_code
order by a.sector_code;
OLD NEW
-----------
014 --- S09
D01 --- S22
D02 --- S42
D03 --- S20
D04 --- S50
D05 --- S31
D06 --- S07
D07 --- S30
D08 --- S37
D09 --- S25
D10 --- S39
D11 --- S29
D12 --- S44
D13 --- S46
D14 --- S40
D15 --- S23
D16 --- S21
D17 --- S16
D19 --- S15
D20 --- S47
D21 --- S43
D22 --- S13
D23 --- S02
D24 --- S03
D24 --- S04
D24 --- S06
D24 --- S32
D25 --- S05
O01 --- S12
O02 --- S41
O03 --- S44
O04 --- S46
O05 --- S33
O06 --- S49
O08 --- S08
O09 --- S14
O10 --- S48
O11 --- S17
O12 --- S19
O13 --- S38
O15 --- S01
O16 --- S24
O17 --- S34
O19 --- S35
O20 --- S28
P01 --- S43
P02 --- S45
P03 --- S01
P04 --- S26
P05 --- S10
P06 --- S11
P07 --- S48
P08 --- S27
Now i have to update the sector_code column in procurement_action_sector table with the values in the new_sector_code of sector_lookup_old table
pls help me how do i do this
This is very urgent
Thanks
Vinod
|
|
|
|
| Re: help me please [message #41285 is a reply to message #41283] |
Fri, 13 December 2002 17:18  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
update procurement_action_sector pas
set sector_code = (select new_sector_code
from sector_lookup_old sl
where sl.sector_code = pas.sector_code);
This approach will update every row in pas, regardless of whether a matching code is found (non-matches will be set to NULL).
|
|
|
|
|
|
| Re: help me please [message #41296 is a reply to message #41283] |
Mon, 16 December 2002 07:12 |
Vinod
Messages: 76
Registered: April 1999
|
Member |
|
|
I did the update as u had told. But it gave me the following error, pls help me
update procurement_action_sector pas
set sector_code = (select new_sector_code
from sector_lookup_old sl where sl.sector_code = pas.sector_code);
set sector_code = (select new_sector_code
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
please help me
Vinod
|
|
|
|
| Re: help me please [message #41301 is a reply to message #41296] |
Mon, 16 December 2002 10:54 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Well, the error message should give you a hint here - apparently a sector code(s) appear more than once in your lookup table. The subquery finds more than one row - how is Oracle supposed to know which row to use to update the base table?
If you don't care which one of the matching rows is used, you can just add ' and rownum = 1' to the WHERE clause. If you do care which row is used, you will have to supply that filter logic.
|
|
|
|
| Re: help me please [message #41304 is a reply to message #41301] |
Mon, 16 December 2002 13:07 |
Vinod
Messages: 76
Registered: April 1999
|
Member |
|
|
i did the following
update procurement_action_sector a
set sector_code=(select new_sector_code
from sector_lookup_old b
where a.sector_code=b.sector_code
and a.ofda_division_code=b.ofda_division_code);
it gave the following error
update procurement_action_sector a
*
ERROR at line 1:
ORA-02291: integrity constraint (OFDA_FIN.FK_PA_SECTOR_SECTOR) violated - parent key not found
I checked the data in the parent table. all the combination exists
when i do the following
update procurement_action_sector a
set sector_code=(select new_sector_code
from sector_lookup_old b
where a.sector_code=b.sector_code
and a.ofda_division_code=b.ofda_division_code
and a.ofda_division_code='DRM');
it gave the following error
update procurement_action_sector a
*
ERROR at line 1:
ORA-01407: cannot update ("OFDA_FIN"."PROCUREMENT_ACTION_SECTOR"."SECTOR_CODE") to NULL
where does the problem lies, i am just not able to understand, pls let me know
Thanks
Vinod
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
