Update statement error

[ichervin]

Hi All,

I have an update statement that when I run it I get the following error:

LMA.SYSTEM: invalid identifier

Update statement:

update inna_set_system
set (add_name, add_no,address_2, add_city, add_state, add_zip,alpha_udf8)=
(select add_name, add_no, addr_2,add_city, add_state, add_zip,alpha_udf8
from load_maint_addr lma)
where inna_set_system.system = lma.system

How can I fix it. Please help. Thanks.

[jkoopmann]

just need to bring the where clause inside the select statement.

update inna_set_system
set (add_name, add_no,address_2, add_city, add_state, add_zip,alpha_udf8)=
(select add_name, add_no, addr_2,add_city, add_state, add_zip,alpha_udf8
from load_maint_addr lma
where inna_set_system.system = lma.system
)

[ichervin]

I did that and now I am getting another error:

ORA-01427: single-row subquery returns more than one row

Please help.

[jkoopmann]

that means that the number of rows returned by
(select add_name, add_no, addr_2,add_city, add_state, add_zip,alpha_udf8
from load_maint_addr lma)
is more than one
and that it can not update a single row with more than one set of values.

you need to fine-tune the select statement so that only one row is returned by a more exact match for the row you want to update.