single-row subquery returns more than one row

[arunmen]

Hi folks,

I have an situation like I have an table named Location where by it stores all
the details of location. In the table I have 2 columns State, city where by I have some duplicate values in the loc table like

for eg:

State City

MH Aurangbad
BH Aurangbad
MP Aurangbad

I need to update the values of the city column with the prefix of state like


State City

MH Aurangbad - MH
BH Aurangbad - BH
MP Aurangbad - MP

I have written an query like

UPDATE LOC
SET CITY = (SELECT CITY || ' - '|| SUBSTR(STATE,1,2)
FROM LOC)
WHERE STATE IN ('MH','BH','MP');

But I am getting an error message like ORA-01427: single-row subquery returns more than one row.

Pls do find an solution for the same.

Arun

[tedlaraghu]

Update Loc L
Set L.city = (select City||' - '||substr(state,1,2) From Loc L1 Where L.state = L1.state)
Where L.state In ('MH',BH','MP')

[arunmen]

Update Loc L
Set L.city = (select City||' - '||substr(state,1,2) From Loc L1 Where L.state = L1.state)
Where L.state In ('MH',BH','MP')

Dear tedlaraghu],

First of all I would like to thank you for helping me out. Since I am new to this SQL I wouls also like you to explain me regarding the code, because you have mentioned 2 aliases for the same table LOC. It's bit confusing.So pls do explain me about the code.

Arun

[arunmen]

Dear tedlaraghu],

First of all I would like to thank you for helping me out. Since I am new to this SQL I wouls also like you to explain me regarding the code, because you have mentioned 2 aliases for the same table LOC. It's bit confusing.So pls do explain me about the code.

Arun

Hi,

When I execute the same code I am getting an error messages stating that
ORA-00904: "L"."STATE": invalid identifier. I tried but I am not able to resolve it.
Pls look into this.

Arun

[debasisdas]

Why not use

update loc set city = city ||'-'||state;