| Pls help me its urgent [message #41297] |
Mon, 16 December 2002 07:24  |
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
i did try with the following statement but i got the following error
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
i also tried 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
group by a.sector_code,b.new_sector_code);
i got the same error
pls help me how do i do this
This is very urgent
Thanks
Vinod
|
|
|
|
| Re: Pls help me its urgent [message #41299 is a reply to message #41297] |
Mon, 16 December 2002 08:16  |
Jon
Messages: 483
Registered: May 2001
|
Senior Member |
|
|
|
You have several new sector codes for D24. Which do you want it to update with? That's the question Oracle can't answer and why you're having the problem. If this is a lookup table, you should only have one match and so need to correct the data. If you don't care (and I don't understand why you wouldn't) which it updates with, you could select distinct.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
