-
update scirpt got ora-01427 when try to update from another table
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?
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Try distinct inside the sub query
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);
-
DISTINCT might return several distinct values, you want just one
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|