Oracle Error Single-row Subquery Returns
Contents |
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java Clipart
Ora-01427 Single-row Subquery Returns In Update Statement
Techie Humor Advertisement Oracle Basics ALIASES AND AND & OR BETWEEN single row subquery returns more than one row while updating COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL
Ora-01427 Update Multiple Rows
IS NULL JOIN LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE SELECT SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced Oracle Cursors Oracle Exception Handling Oracle Foreign Keys single row subquery returns multiple rows in update statement Oracle Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions Advanced Functions Oracle / PLSQL: ORA-01427 Error Message Learn the cause and how to resolve the ORA-01427 error message in Oracle. Description When you encounter an ORA-01427 error, the following error message will appear: ORA-01427: single-row subquery returns more than one row ora 01427 in update statement Cause You tried to execute a SQL statement that contained a SQL subquery that returns more than one row. Resolution The option(s) to resolve this Oracle error are: Option #1 Rewrite your query so that the subquery only returns one row. Option #2 Change your query to use one of the following with your subquery results: ANY ALL IN NOT IN For example, if you tried to execute the following SQL statement: SELECT * FROM orders WHERE supplier_id = (SELECT supplier_id FROM suppliers WHERE supplier_name = 'IBM'); And there was more than one record in the suppliers table with the supplier_name of IBM, you would receive the following message: The most common way to correct this SQL statement is to use the IN condition as follows: SELECT * FROM orders WHERE supplier_id IN (SELECT supplier_id FROM suppliers WHERE supplier_name = 'IBM'); Share this page: Advertisement Back to top Home | About Us | Contact Us | Testimonials | Donate While using this site, you agree to have read and accepted
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers
Single-row Subquery Returns More Than One Row Insert
or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question multiple row subquery in oracle x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them;
Single-row Subquery Returns More Than One Row In Select
it only takes a minute: Sign up UpdateError: Receiving Error ORA - 01427 Single-row subquery returns more than one row up vote 3 down vote favorite I am trying to update a column based on another column in the same https://www.techonthenet.com/oracle/errors/ora01427.php table (student table) and a column from another table (school table) Code is: update student_table set student_code = (select l.student_code from school_table l, student_table n where l.school = n.schoolname) I get the following error ORA - 01427 Single-row subquery returns more than one row Any help would be appreciated. sql oracle sql-update ora-01427 share|improve this question edited May 1 '12 at 5:29 Burhan Ali 1,75711334 asked Apr 2 '12 at 3:14 user1307149 7431720 add a comment| 6 Answers 6 active http://stackoverflow.com/questions/9970480/updateerror-receiving-error-ora-01427-single-row-subquery-returns-more-than-o oldest votes up vote 2 down vote accepted It would be helpful to have a plain English explanation of what you are trying to accomplish. Having said that, it appears to me that you can accomplish what you want to do with the following SQL [assuming one to many relationship between school_table and student_table] having the inner select as a corelated sub-query with the outer update statement: update student_table set student_code = (select l.student_code from school_table where school_table.school = student_table.schoolname) ; Hope this helps. Regards, Roger share|improve this answer answered Apr 3 '12 at 14:15 Roger Cornejo 34215 Thanks Roger I had to change it around some but it worked! –user1307149 Apr 22 '12 at 1:49 add a comment| up vote 4 down vote If you run your subquery you'll find it returning more than one row. You are trying to update a column to be equal to the result of your subquery so it expects only one value. You should limit your subquery to only return one row such as using max() or min() or, perhaps you meant to join to the outer student_table? Try: update student_table n set student_code = (select l.student_code from school_table l where l.school = n.schoolname); share|improve this answer answered Apr 2 '12 at 3:24 John Doyle 4,37742334 Ok, I see what you are saying. Since my inner query is returning more than one value it is giving
Digital Records Management Enterprise Content Management Strategy Digital Asset Management Oracle Imaging & Process Management Web Content Management Oracle WebCenter Portal Enterprise Portal Support Enterprise Portal Strategy https://www.tekstream.com/resources/ora-01427-error-message/ Enterprise Portal Upgrade Oracle WebCenter Sites Sourcing Staffing & Recruiting Recruiting http://www.oracleracexpert.com/2012/05/ora-01427-single-row-subquery-returns.html Managed Services Candidate Registration Technical Focus Client Opportunities Support Solutions Training Legacy to Oracle WebCenter Oracle Documents Cloud Service Next Generation AP Automation & Dynamic Discounting Oracle WebCenter Contract Lifecycle Management (CLM) Search ORA-01427: Subquery returns more than one rowYou are here: row subquery Home / Resources / ORA-01427: Subquery returns more than one row ORA-01427 Operating within database software can sometimes feel like navigating a minefield. The vast expanse of data sets, tables and functions can leave a user tip-toeing throughout, careful not to enter a mistaken query or improper syntax that could halt the data row subquery returns in an instant. A software as user-friendly and accessible as Oracle can even occasionally feel problematic, perhaps seeming most daunting when writing statements for it to process. The ORA-01427 error can be the manifestation of these fears. Fortunately, the error has a couple of basic approaches that will remedy the situation and have your code back up and running in no time. The Problem The ORA-01427 is a straightforward query error in Oracle. The cause of the error is a subquery returning more than one row of information. This error in multi-row returns on the subquery originates from an outer query failing to use appropriate, designated keywords to specify values for comparison in the subquery. Before we continue with solving this error, let us briefly discuss subqueries in Oracle. The definition of an Oracle subquery is fairly tautological: a subquery is a query within a query. To review, a query is simply a function used to ret
Guard Architecture Understand Oracle Real Application Cluster Understand the Power of Oracle RMAN - Page 47 Migrate Non-ASM Database to ASM Using RMAN Modifying the VIP Address or VIP Hostname in RAC Duplicate RAC Database Using RMAN Categories 12C New Features (12) Active DataGuard (1) Administration (33) Agile Patch (3) alert.log errors (12) Analysis Office (1) Articles/Best Practices (19) ASM (14) Audit (2) Backup/Recovery (14) BOXI (3) Certification (6) Clone (1) Cloud (5) Control file (1) Corruption/Recovery (4) Data Pump (6) Database Replay (1) Database Upgrade (5) DataGuard (2) DB Security (2) DBUA (3) diagnostic Tools (2) Documentation (1) Downloads/Tools (8) Duplicate Database (4) Dynamic Tiering (3) EM Grid Control (7) Essbase (3) Event Viewer (1) Export/Import (7) Flash Recovery Area (3) Free Training (2) GoldenGate (4) HA100 (1) HA200 (1) HANA (2) HANA SPS07 (2) Heterogeneous (5) High Availability (15) Hyperion Errors (3) InnoDB (1) Installation (6) Java Error (4) JDBC Driver (3) License (2) Linux Admin (3) Linux-Errors (7) Load Balancer (1) Migrate (3) Migration (4) MOPatch (2) Multitenant (7) MySQL Access (1) MySQL Admin (7) MySQL Backup/Restore (1) MySQL Errors (5) OCR - VOTE (8) ODBC (6) OEM Agent (1) OLAP Connection (1) Opatch (6) Ora-Errors (33) Oracle 10g (27) Oracle 10g New Feat (7) Oracle 11g (34) Oracle 11g New Feat (14) Oracle 12C (20) Oracle 8i (5) Oracle 9i (11) Oracle ACE (1) Oracle Agile (7) Oracle APEX (1) Oracle Beta Program (1) Oracle BI (1) Oracle E-Business Suite (1) Oracle Hyperion (3) Oracle In-Memory (3) Oracle OpenWorld (2) Oracle RAC (5) Oracle Support (4) OUI (4) Packt Publishing (2) Patch/Bug Fix (14) Performance (1) PL/SQL (6) Planning (2) Pluggable Database (7) PSU (1) RAC Admin (17) RAC-Errors (7) RCONFIG (1) Redo Log (6) Replication (2) RMAN Backup (25) RMAN Recovery (29) RMAN-ERROR (6) SAML (1) SAP (14) SAP Basis (6) SAP HANA (14) SAP HANA Client (2) SAP HANA Shine (2) SAP HANA Studio (6) SAP Note (1) Security (3) Smart Data Access (1) Smart View (2) Spfile and Pfile (6) SQL (10) SQLException (1) SQLNET (2) SSL (1) SSO (2) Standby (1) STARTUP (2) Storage (1) SYBASE (1) T-CODE (1) tabadmin (2) Tableau Desktop (1) Tableau Server (1) Tableau Server 9.1.2 (1) Tableau Server 9.1.3 (2) TAF (3) TNS Errors (2) Tools (2) Trace File (5) Transportable TBS (3) Tuning (5) UNDO Segment (4) UNIX (6) Upgrade (1) Upgrade 8i to 10g (1) Upgrade to 10.2.0.4 (4) UTL_MAIL (2) UTL_SMTP (2) Vedio Tutorials (1) Webinar (4) WebLogic (2) White Paper (2) Blog Archive &#