Update command with sub query : Sub query : Select Clause SQL / MySQL


SQL / MySQL » Select Clause » Sub query »

 

Update command with sub query



/*

mysql> UPDATE Articles
    -> SET ArticleTitle='The Way of Zen', Copyright=1957
    -> WHERE ArticleID=
    ->    (
    ->       SELECT ab.ArticleID
    ->       FROM Authors AS a, AuthorArticle AS ab
    ->       WHERE a.AuthID=ab.AuthID AND a.AuthorLastName='Yin'
    ->    );
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0


*/
Drop table Articles;
Drop table Authors;
Drop table AuthorArticle;
Drop table ArticleOrders;


CREATE TABLE Articles (
   ArticleID SMALLINT NOT NULL PRIMARY KEY,
   ArticleTitle VARCHAR(60NOT NULL,
   Copyright YEAR NOT NULL
)
ENGINE=INNODB;


INSERT INTO Articles VALUES (12786'How write a paper', 1934),
                            (13331'Publish a paper', 1919),
                            (14356'Sell a paper', 1966),
                            (15729'Buy a paper', 1932),
                            (16284'Conferences', 1996),
                            (17695'Journal', 1980),
                            (19264'Information', 1992),
                            (19354'AI', 1993);


CREATE TABLE Authors (
   AuthID SMALLINT NOT NULL PRIMARY KEY,
   AuthorFirstName VARCHAR(20),
   AuthorMiddleName VARCHAR(20),
   AuthorLastName VARCHAR(20)
)
ENGINE=INNODB;


INSERT INTO Authors VALUES (1006'Henry', 'S.', 'Thompson'),
                           (1007'Jason', 'Carol', 'Oak'),
                           (1008'James', NULL, 'Elk'),
                           (1009'Tom', 'M''Ride'),
                           (1010'Jack', 'K''Ken'),
                           (1011'Mary', 'G.', 'Lee'),
                           (1012'Annie', NULL, 'Peng'),
                           (1013'Alan', NULL, 'Wang'),
                           (1014'Nelson', NULL, 'Yin');


CREATE TABLE AuthorArticle (
   AuthID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL,
   PRIMARY KEY (AuthID, ArticleID),
   FOREIGN KEY (AuthIDREFERENCES Authors (AuthID),
   FOREIGN KEY (ArticleIDREFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO AuthorArticle VALUES (100614356)
                              (100815729)
                              (100912786)
                              (101017695),
                              (101115729)
                              (101219264)
                              (101219354)
                              (101416284);
CREATE TABLE ArticleOrders
(
   OrderID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL,
   Quantity SMALLINT NOT NULL,
   PRIMARY KEY (OrderID, ArticleID),
   FOREIGN KEY (ArticleIDREFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO ArticleOrders VALUES (101133311)
                                 (101127861)
                                 (101162842)
                                 (102193541),
                                 (102157293)
                                 (103127862)
                                 (103192641)
                                 (103133311),
                                 (103143562)
                                 (104193541)
                                 (105157291)
                                 (105143562),
                                 (106162842)
                                 (106133311)
                                 (107127863)
                                 (108193541),
                                 (108162844)
                                 (109157291)
                                 (110133312)
                                 (110127862),
                                 (110143562)
                                 (111143562);
  
UPDATE Articles
SET ArticleTitle='The Way of Zen', Copyright=1957
WHERE ArticleID=
   (
      SELECT ab.ArticleID
      FROM Authors AS a, AuthorArticle AS ab
      WHERE a.AuthID=ab.AuthID AND a.AuthorLastName='Yin'
   );


           
       



Leave a Comment / Note


 
Verification is used to prevent unwanted posts (spam). .

Follow Navioo On Twitter

SQL / MySQL

 Navioo Select Clause
» Sub query