Updating multiple rows in pl sql

Rated 4.65/5 based on 745 customer reviews

I want to UPDATE a field of table with the results of another query, (sub query), but my sub quesry contains a Group By and so returns multipel rows.

UPDATE TABLE_ONE SET TOTAL_SALES =(SUBQUERY) The Subquery: Select COUNT(*) from TABLE_TWO where TRANSACTION_TYPE="SALE" GROUP BY PERSON_ID QUERIES together UPDATE TABLE_ONE SET TOTAL_SALES=(SELECT Count(*) FROM TABLE_TWO GROUP BY PERSON_ID) This Subquery return multiple rows and hence teh ORA-01427 error.

This rule allows you to update values on multiple rows in a single UPDATE statement.

create table Employee( 2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 / Table created.

Oracle Tutorials Oracle Tutorials - Update Values on Multiple Rows By: (Continued from previous topic...) How To Update Values on Multiple Rows?

If the WHERE clause in an UPDATE matches multiple rows, the SET clause will be applied to all matched rows.

Some examples of DDL can be can be one condition, or multiple, dependent on what you need. be that you want to update just customers that are located in one area of the world.So, with the syntax shown earlier in mind, let us put things together, and look at some practical examples.BTW – If you are interested in where I get the data for these examples – they are from the sample schemas (OE, HR, etc.) that comes with most Oracle database installations.SQL select * from Employee 2 / ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.SQL select * from employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION ---- ---------- ---------- --------- --------- ---------- ---------- --------------- 01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 L. Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 L. Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected.

Leave a Reply