update scirpt got ora-01427 when try to update from another table

[sfgaldba]

I am not sure why this is wrong, or how to get around this? Can any SQL experts help?

update ehtemp.application_xml_q2007 a
set a.xml_name = b.xml_name from ehtemp.application_xml_info_add where a.form_id = b.form_id;

I get ORA-00933: SQL command not properly ended

When I do this:

update application_xml_q2007 a
set a.xml_name = (select b.xml_name from application_xml_info_add b where a.form_id b.form_id);
I get:
ORA-01427: single-row subquery returns more than one row

How can I fix my script to make update work?

[PAVB]

Your second query has a basically correct syntax but, problem is your inline query should return a single value and as Oracle complains it's returning several rows.

By the way... on
select b.xml_name from application_xml_info_add b where a.form_id b.form_id)

I can see no "=" sign in between a.form_id and b.form_id

[sfgaldba]

update application_xml_q2007 a
set a.xml_name = (select b.xml_name from application_xml_info_add b where a.form_id = b.form_id);


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

I am not sure how to fix this...anyone? Thanks.

[PAVB]

Problem here is ...

(select b.xml_name from application_xml_info_add b where a.form_id = b.form_id)

... is returning more than one row meaning, more than one value for b.xml_name therefore Oracle doesn't know which one you want to use to update row on application_xml_q2007 table and cancels with ORA-01427

You have to do something to force your inline query to return a single row.

Question: Are all b.xml_name values the same for a specific form_id?

If your answer is YES that means you don't care about which one is used to update your table therefore you can resort to something like...

(select * from (select b.xml_name from application_xml_info_add b where a.form_id = b.form_id) where rownum < 2);

If your answer is NO you have to find another way of forcing inline query to return single row... tip: work on WHERE clause.

[uchaganti]

Try distinct inside the sub query

update application_xml_q2007 a
set a.xml_name = (select DISTINCT b.xml_name from application_xml_info_add b where a.form_id = b.form_id);

[PAVB]

DISTINCT might return several distinct values, you want just one :rolleyes: