Update: Based On Multiple Returned Values

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

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'
);

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.