I'm a sql newbie witha n oracle query giving an ORA-01427 Error
UPDATE FIPS_CODE
SET GROUP_CODE = (SELECT group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd)
WHERE EXISTS (SELECT group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd)
there are 2 rows in group_code table and one in fips_code
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Try to add and rownum=1 to your subquery conditions if you do not care about the value from the list or do sure that they are the same.
UPDATE FIPS_CODE
SET GROUP_CODE = (SELECT group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd and rownum=1)
WHERE EXISTS (SELECT group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd)
A better way to do that, would probably be to use the DISTINCT keyword - it better protects against future duplicates.
UPDATE FIPS_CODE
SET GROUP_CODE = (SELECT DISTINCT group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd)
WHERE EXISTS (SELECT group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd)
If you have 2 rows with the same value, it will return just one value. If you have different values, it will still return an error. The Rownum=1 option will just return the first row.
I believe that sjwales correctly identified the problem in his/her first post. I disagree though with the suggestion to use "distinct" for two reasons:
1. as he/she indicated, that will not help you if there really are two (or more) different values
2. this word can cause a huge performance penalty if it is used in Oracle queries where it is not needed, so I don't want to see you get in the habit of just including this when you may not need it.
Use one of these,
1.
UPDATE FIPS_CODE
SET GROUP_CODE = (SELECT distinct group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd)
WHERE EXISTS (SELECT group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd);
2.
UPDATE FIPS_CODE
SET GROUP_CODE = (SELECT group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd and rownum < 2)
WHERE EXISTS (SELECT group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd)
Business Accounts
Answer for Membership
by: sjwalesPosted on 2007-06-28 at 08:14:45ID: 19381520
A good explanation of this error is here: http://www.techonthenet.co
This line:
SET GROUP_CODE = (SELECT group_code FROM GROUPS WHERE GROUPS.FIPS = fipscd)
looks like it could be the culprit.
The "=" statement here is expecting the subquery to return a single row. The error message would seem to indicate that it's returning more than one row. Since you can't set the value GROUP_CODE to more than one value as is being returned by the subquery, you get the error.
Refine your subquery so that it returns only one row.