Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: UPDATE: based on multiple returned values Jump to:  
Category: >> Oracle PL/SQL >> UPDATE: based on multiple returned values Bookmark and Share

<< lastnext >>

Snippet Name: UPDATE: based on multiple returned values

Description: Update based on multiple returned values from a query.

Also see:
» UPDATE: Update a partitioned table
» UPDATE: Update based on a record
» UPDATE: Update Object Table
» UPDATE: with RETURNING clause
» UPDATE: Nested Table Update example
» UPDATE: Correlated Update
» UPDATE: Update from a SELECT statement
» UPDATE: Update based on a query
» UPDATE: Update multiple rows
» UPDATE: update a specific record
» UPDATE: Single row
» UPDATE

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 04th, 2009

UPDATE <table_name> <alias>
SET (<column_name>,<column_name> ) = (
   SELECT (<column_name>, <column_name>)
   FROM <table_name>
   WHERE <alias.column_name> = <alias.column_name>)
WHERE <column_name> <condition> <value>;
 
/*-------------------------------------------------*/
 
UPDATE indirect_customerb b
   SET (b.class_of_trade) =
       (SELECT a.classoftrade
          FROM gpo_memberships a
              ,(SELECT name,importance
                  FROM COT_importance) c
         WHERE a.dea = b.industry_id
           AND a.classoftrade = c.name
           AND c.importance = (SELECT MIN(importance)
                                 FROM COT_importance
                                WHERE c.name = name)
       )
 WHERE EXISTS (SELECT 'foo' FROM gpo_memberships c
                WHERE c.dea = b.industry_id)
 
/*-------------------------------------------------
mysql> select * from Course;
+----------+----------------------+---------+
| CourseID | Name                 | Credits |
+----------+----------------------+---------+
|        1 | Mediaeval Romanian   |       5 |
|        2 | Philosophy           |       5 |
|        3 | History of Computing |       5 |
+----------+----------------------+---------+
3 rows in set (0.00 sec)
 
mysql> SELECT Name FROM Course
    -> WHERE CourseID IN
    -> (
    -> SELECT CourseID from EXAM
    -> WHERE SustainedOn='26-MAR-03'
    -> );
Empty set, 1 warning (0.00 sec)
 
 
-------------------------------------------------*/
 
DROP TABLE Course;
DROP TABLE Exam;       
 
CREATE TABLE Course (
   CourseID INT NOT NULL PRIMARY KEY,
   Name     VARCHAR(50),
   Credits  INT)
TYPE = InnoDB;
 
CREATE TABLE Exam (
   ExamID      INT NOT NULL PRIMARY KEY,
   CourseID    INT NOT NULL,
   SustainedOn DATE,
   Comments    VARCHAR(255),
 
   INDEX       examcourse_index(CourseID)
 
 
)TYPE = InnoDB;
 
 
INSERT INTO Course (CourseID,Name,Credits) VALUES (1,'Mediaeval Romanian',5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (2,'Philosophy',5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (3,'History of Computing',5);
 
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES 
      (1,1,'2003-03-12','JavaScript');
 
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES 
      (2,1,'2003-03-13','Java');
 
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES 
      (3,2,'2003-03-11','Python');
 
INSERT INTO Exam (ExamID,CourseID,SustainedOn) VALUES 
      (4,3,'2003-03-18','Swing');
 
 
SELECT * FROM Course;
 
SELECT Name FROM Course
WHERE CourseID IN
(
SELECT CourseID FROM EXAM
WHERE SustainedOn='26-MAR-03'
);
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 105 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?