Error 01427 Oracle
Contents |
Digital Records Management Enterprise Content Management Strategy Digital Asset Management Oracle Imaging & Process Management Web Content Management Oracle WebCenter Portal Enterprise Portal
Single-row Subquery Returns More Than One Row Oracle
Support Enterprise Portal Strategy Enterprise Portal Upgrade Oracle WebCenter Sites 01427. 00000 - single-row subquery returns more than one row Sourcing Staffing & Recruiting Recruiting Managed Services Candidate Registration Technical Focus Client Opportunities Support Solutions ora 01427 in update statement Training Legacy to Oracle WebCenter Oracle Documents Cloud Service Next Generation AP Automation & Dynamic Discounting Oracle WebCenter Contract Lifecycle Management (CLM) Search ORA-01427:
Ora-01427 Single-row Subquery Returns In Update Statement
Subquery returns more than one rowYou are here: 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
Ora-01427 Update
a mistaken query or improper syntax that could halt the data 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
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle ora-01427 update multiple rows Books Oracle Scripts Ion Excel-DB Don Burleson Blog
Single Row Subquery Returns More Than One Row While Updating
ORA-01427: single-row subquery returns more than one multiple row subquery in oracle row tips Oracle Error Tips by Burleson Consulting (S. Karam) The Oracle docs note this on the ORA-01427 error*: ORA-01427 single-row subquery returns more than https://www.tekstream.com/resources/ora-01427-error-message/ one row Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row. Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is http://www.dba-oracle.com/t_ora_01427_single_row_subquery_returns_more_than_one_row.htm retrieved. On Oracle Server and PL/SQL User Forum, Adam Wilbur received an ORA-01427 error code and received this information: Question Posted by 'Adam Wilbur' on April 21, 2001 at 20:37:00 EST: Here's what I am trying to do... UPDATE TABLEA SET TABLEA.COLUMN1 = (select TABLEB.COLUMN1 FROM TABLEA, TABLEB WHERE TABLEB.COLUMN2 = 'String Value' AND TABLEA.COLUMN3 = TABLEB.COLUMN3 AND TABLEA.COLUIMN4 = TABLEB.COLUMN4 AND TABLEB.VALUENAME = 'String Value'); This query returns multiple rows, all of which I need to update in TABLEA. How do I do this without getting ORA-01427 error? Answer Dmytro offered this advice to resolve ORA-01427: 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. Single Row Subquery A single row subquery returns only one row. It can be used with the equal comparison operators (=,<,>,<>, etc). SQL> select2 order_number3 from4 sales5 where quantity = (select6 max(quantity)7 from8 sales); ORDER_NUMBER --------------------O161 Here, we
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 http://stackoverflow.com/questions/9970480/updateerror-receiving-error-ora-01427-single-row-subquery-returns-more-than-o Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, http://www.oracleracexpert.com/2012/05/ora-01427-single-row-subquery-returns.html just like you, helping each other. Join them; 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 row subquery to update a column based on another column in the same 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 row subquery returns Ali 1,73711334 asked Apr 2 '12 at 3:14 user1307149 7431720 add a comment| 6 Answers 6 active 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.schoolna
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 (