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






  1. SQL / MySQL
  2. Select Clause
  3. 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(60) NOT 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 (AuthID) REFERENCES Authors (AuthID),
   FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO AuthorArticle VALUES (1006, 14356), 
                              (1008, 15729), 
                              (1009, 12786), 
                              (1010, 17695),
                              (1011, 15729), 
                              (1012, 19264), 
                              (1012, 19354), 
                              (1014, 16284);
CREATE TABLE ArticleOrders
(
   OrderID SMALLINT NOT NULL,
   ArticleID SMALLINT NOT NULL,
   Quantity SMALLINT NOT NULL,
   PRIMARY KEY (OrderID, ArticleID),
   FOREIGN KEY (ArticleID) REFERENCES Articles (ArticleID)
)
ENGINE=INNODB;


INSERT INTO ArticleOrders VALUES (101, 13331, 1), 
                                 (101, 12786, 1), 
                                 (101, 16284, 2), 
                                 (102, 19354, 1),
                                 (102, 15729, 3), 
                                 (103, 12786, 2), 
                                 (103, 19264, 1), 
                                 (103, 13331, 1),
                                 (103, 14356, 2), 
                                 (104, 19354, 1), 
                                 (105, 15729, 1), 
                                 (105, 14356, 2),
                                 (106, 16284, 2), 
                                 (106, 13331, 1), 
                                 (107, 12786, 3), 
                                 (108, 19354, 1),
                                 (108, 16284, 4), 
                                 (109, 15729, 1), 
                                 (110, 13331, 2), 
                                 (110, 12786, 2),
                                 (110, 14356, 2), 
                                 (111, 14356, 2);
  
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'
   );








Related examples in the same category

1.Subqueries As Calculated Columns: Simple Subqueries
2.Subqueries in the WHERE Clause 2
3.Subqueries That Return Multiple Results
4.Sub queries
5.Sub query with string concatenate
6.Sub query with not equal and order
7.Sub query with calculation
8.Delete command with sub query
9.SubSELECTs Syntax Variants
10.Uses a subquery to return an AuthID value
11.Use a not equal (<>) comparison operator in the WHERE clause to introduce the subquery
12.Subquery uses an aggregate function to arrive at a value that the outer statement can use
13.Nested subquery
14.Four-level nested subquery with alias
15.Nested subquery and where clause
16.Minus value from subquery
17.Subquery and equals operator(ERROR 1242 (21000): Subquery returns more than 1 row)
18.Row values and subquery
19.Nested three level subquery
20.Greater than the result of subquery
21.Equals to the result of subquery
22.Row values comparison for subquery
23.Select from the result of subquery
24.Value calculation of subquery
25.Subquery as a table
26.Alias subquery
27.Calculation in subquery
28.Using function with result from subquery
29.Create constant value in subquery
30.Calculation in subquery and use the result from outside
31.Equals operator with subquery
32.Less than and subquery(ERROR 1242 (21000): Subquery returns more than 1 row)
33.Less than data type value from subquery
34.<=> and subquery
35.Less than two values in subquery
36.Greater than two values in subquery
37.Get substring for a value from subquery
38.Mix constant value and subquery
39.Using the value from subquery with in operator
40.Pair value within in operator and subquery
41.In a list of value from subquery
42.In operator and subquery
43.Nested subquery and in operator
44.Not in and subquery
45.Between...and opertor and subquery
46.Count value in subquery
47.Add up count value from subquery
48.From a subquery
49.Aggregate function in subquery
50.Having clause with subquery
51.Count and subquery in Having clause
52.Subquery with having clause
53.Order by value from subquery
54.Subquery with limit clause
55.Insert statement with subquery
56.Use a derived table.
57.Searching for Titles Without Authors
58.Compare to aggregate function
59.Data calculation inside in operator
60.COMPARISON OPERATORS WITH SUBQUERIES
61.Select from three subqueries
62.Select from two subqueries
63.Row value comparison with select statement
64.Compare two data type value together
65.Nested subqueries
66.Two USING clauses, rather than ON clauses
67.Finding people who didn't send mail to themselves