SQL/MySQL/Insert Delete Update/Update

Материал из SQL эксперт
Перейти к: навигация, поиск

Assign value in select clause

   <source lang="sql">

/* mysql> select * from employee; +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | id | firstname | lastname | title | age | yearofservice| salary | perks | email | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru | | 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru | | 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru | | 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru | | 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru | | 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru | | 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru | | 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru | | 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru | | 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru | | 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru | | 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru | | 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru | | 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru | | 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru | | 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru | | 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru | | 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru | | 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru | | 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ 20 rows in set (0.00 sec) mysql> UPDATE employee SET 

   -> salary=220000, perks=55000
   -> WHERE title="CEO";

Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0

  • /

Drop table employee; CREATE TABLE employee ( 

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru"); select * from employee; UPDATE employee SET salary=220000, perks=55000 WHERE title="CEO"; select * from employee; 

      </source>


Do PLUS calculation in where clause

   <source lang="sql">

/* mysql> select * from employee; +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | id | firstname | lastname | title | age | yearofservice| salary | perks | email | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ | 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru | | 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru | | 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru | | 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru | | 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru | | 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru | | 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru | | 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru | | 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru | | 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru | | 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru | | 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru | | 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru | | 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru | | 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru | | 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru | | 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru | | 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru | | 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru | | 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru | +----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+ 20 rows in set (0.00 sec) mysql> UPDATE employee SET salary = salary + 20000, perks = perks + 5000 

   -> WHERE title="CEO";

Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0

  • /

Drop table employee; CREATE TABLE employee ( 

   id int unsigned not null auto_increment primary key,
   firstname varchar(20),
   lastname varchar(20),
   title varchar(30),
   age int,
   yearofservice int,
   salary int,
   perks int,
   email varchar(60)

);

INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru"); INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru"); select * from employee; UPDATE employee SET salary = salary + 20000, perks = perks + 5000 WHERE title="CEO"; select * from employee; 

      </source>


Modifying Row Data

   <source lang="sql">

/* Create the table */ Drop TABLE Professor; CREATE TABLE Professor ( 

  ProfessorID INT NOT NULL PRIMARY KEY,
  Name        VARCHAR(50) NOT NULL)

TYPE = InnoDB; /* Prepare the data */ INSERT INTO Professor (ProfessorID,Name) VALUES (1,"John Jones"); INSERT INTO Professor (ProfessorID,Name) VALUES (2,"Cury Butz"); INSERT INTO Professor (ProfessorID,Name) VALUES (3,"JJ Smith");

/* Real command */

UPDATE Professor SET Name = CONCAT("Prof. ", Name) 

  WHERE ProfessorID > 6;



      </source>


Update records with calculation based on two tables

   <source lang="sql">

/* mysql> select * from Books; +--------+-----------+---------+ | BookID | BookName | InStock | +--------+-----------+---------+ | 1 | Poet | 1934 | | 2 | Ohio | 1919 | | 3 | Angels | 1966 | | 4 | Black | 1932 | | 101 | Writing | 10 | | 102 | News | 17 | | 103 | Angels | 23 | | 104 | Poet | 32 | | 105 | Dunces | 6 | | 106 | Solitude | 28 | | 107 | Postcards | 1992 | | 108 | The | 1993 | +--------+-----------+---------+ 12 rows in set (0.00 sec) mysql> UPDATE Books, Orders 

   -> SET Books.InStock=Books.InStock-Orders.Quantity
   -> WHERE Books.BookID=Orders.BookID
   ->    AND Orders.OrderID=1002;

Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from Books; +--------+-----------+---------+ | BookID | BookName | InStock | +--------+-----------+---------+ | 1 | Poet | 1934 | | 2 | Ohio | 1919 | | 3 | Angels | 1966 | | 4 | Black | 1932 | | 101 | Writing | 9 | | 102 | News | 17 | | 103 | Angels | 23 | | 104 | Poet | 32 | | 105 | Dunces | 6 | | 106 | Solitude | 28 | | 107 | Postcards | 1992 | | 108 | The | 1993 | +--------+-----------+---------+ 12 rows in set (0.01 sec)

  • /

Drop table Books; Drop table Orders;

CREATE TABLE Books ( 

  BookID SMALLINT NOT NULL PRIMARY KEY,
  BookName VARCHAR(40) NOT NULL,
  InStock SMALLINT NOT NULL

) ENGINE=INNODB;

CREATE TABLE Orders ( 

  OrderID SMALLINT NOT NULL PRIMARY KEY,
  BookID SMALLINT NOT NULL,
  Quantity TINYINT (40) NOT NULL DEFAULT 1,
  DateOrdered TIMESTAMP,
  FOREIGN KEY (BookID) REFERENCES Books (BookID)

) ENGINE=INNODB;

INSERT INTO Orders VALUES (1001, 103, 1, "2004-01-12 12:30:00"), 

                         (1002, 101, 1, "2001-02-12 12:31:00"),
                         (1003, 103, 2, "2002-03-12 12:34:00"),
                         (1004, 104, 3, "2003-04-12 12:36:00"),
                         (1005, 102, 1, "2004-05-12 12:41:00"),
                         (1006, 103, 2, "2001-06-12 12:59:00"),
                         (1007, 101, 1, "2002-07-12 13:01:00"),
                         (1008, 103, 1, "2003-08-12 13:02:00"),
                         (1009, 102, 4, "2004-09-12 13:22:00"),
                         (1010, 101, 2, "2005-11-12 13:30:00"),
                         (1011, 103, 1, "2006-12-12 13:32:00"),
                         (1012, 105, 1, "2001-02-12 13:40:00"),
                         (1013, 106, 2, "2002-04-12 13:44:00"),
                         (1014, 103, 1, "2003-06-12 14:01:00"),
                         (1015, 106, 1, "2005-01-12 14:05:00"),
                         (1016, 104, 2, "2003-11-12 14:28:00"),
                         (1017, 105, 1, "2002-03-12 14:31:00"),
                         (1018, 102, 1, "2001-05-12 14:32:00"),
                         (1019, 106, 3, "2003-07-12 14:49:00"),
                         (1020, 103, 1, "2004-01-12 14:51:00");

INSERT INTO Books VALUES (101, "Writing", 12), 

                        (102, "News", 17),
                        (103, "Angels", 23),
                        (104, "Poet", 32),
                        (105, "Dunces", 6),
                        (106, "Solitude", 28);

select * from Books; UPDATE Books, Orders SET Books.InStock=Books.InStock-Orders.Quantity WHERE Books.BookID=Orders.BookID 

  AND Orders.OrderID=1002;

select * from Books; 

      </source>


Update two tables with calculation

   <source lang="sql">

/* mysql> select * from Orders; +---------+--------+----------+---------------------+ | OrderID | BookID | Quantity | DateOrdered | +---------+--------+----------+---------------------+ | 1001 | 103 | 1 | 2004-01-12 12:30:00 | | 1002 | 101 | 1 | 2001-02-12 12:31:00 | | 1003 | 103 | 2 | 2002-03-12 12:34:00 | | 1004 | 104 | 3 | 2003-04-12 12:36:00 | | 1005 | 102 | 1 | 2004-05-12 12:41:00 | | 1006 | 103 | 2 | 2001-06-12 12:59:00 | | 1007 | 101 | 1 | 2002-07-12 13:01:00 | | 1008 | 103 | 1 | 2003-08-12 13:02:00 | | 1009 | 102 | 4 | 2004-09-12 13:22:00 | | 1010 | 101 | 2 | 2005-11-12 13:30:00 | | 1011 | 103 | 1 | 2006-12-12 13:32:00 | | 1012 | 105 | 1 | 2001-02-12 13:40:00 | | 1013 | 106 | 2 | 2002-04-12 13:44:00 | | 1014 | 103 | 1 | 2003-06-12 14:01:00 | | 1015 | 106 | 1 | 2005-01-12 14:05:00 | | 1016 | 104 | 2 | 2003-11-12 14:28:00 | | 1017 | 105 | 1 | 2002-03-12 14:31:00 | | 1018 | 102 | 1 | 2001-05-12 14:32:00 | | 1019 | 106 | 3 | 2003-07-12 14:49:00 | | 1020 | 103 | 1 | 2004-01-12 14:51:00 | +---------+--------+----------+---------------------+ 20 rows in set (0.00 sec) mysql> select * from Books; +--------+-----------+---------+ | BookID | BookName | InStock | +--------+-----------+---------+ | 1 | Poet | 1934 | | 2 | Ohio | 1919 | | 3 | Angels | 1966 | | 4 | Black | 1932 | | 101 | Writing | 9 | | 102 | News | 17 | | 103 | Angels | 23 | | 104 | Poet | 32 | | 105 | Dunces | 6 | | 106 | Solitude | 28 | | 107 | Postcards | 1992 | | 108 | The | 1993 | +--------+-----------+---------+ 12 rows in set (0.00 sec) mysql> UPDATE Books, Orders 

   -> SET Orders.Quantity=Orders.Quantity+2,
   ->    Books.InStock=Books.InStock-2
   -> WHERE Books.BookID=Orders.BookID
   ->    AND Orders.OrderID = 1002;

Query OK, 2 rows affected (0.06 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> select * from Orders; +---------+--------+----------+---------------------+ | OrderID | BookID | Quantity | DateOrdered | +---------+--------+----------+---------------------+ | 1001 | 103 | 1 | 2004-01-12 12:30:00 | | 1002 | 101 | 3 | 2005-10-09 08:51:30 | | 1003 | 103 | 2 | 2002-03-12 12:34:00 | | 1004 | 104 | 3 | 2003-04-12 12:36:00 | | 1005 | 102 | 1 | 2004-05-12 12:41:00 | | 1006 | 103 | 2 | 2001-06-12 12:59:00 | | 1007 | 101 | 1 | 2002-07-12 13:01:00 | | 1008 | 103 | 1 | 2003-08-12 13:02:00 | | 1009 | 102 | 4 | 2004-09-12 13:22:00 | | 1010 | 101 | 2 | 2005-11-12 13:30:00 | | 1011 | 103 | 1 | 2006-12-12 13:32:00 | | 1012 | 105 | 1 | 2001-02-12 13:40:00 | | 1013 | 106 | 2 | 2002-04-12 13:44:00 | | 1014 | 103 | 1 | 2003-06-12 14:01:00 | | 1015 | 106 | 1 | 2005-01-12 14:05:00 | | 1016 | 104 | 2 | 2003-11-12 14:28:00 | | 1017 | 105 | 1 | 2002-03-12 14:31:00 | | 1018 | 102 | 1 | 2001-05-12 14:32:00 | | 1019 | 106 | 3 | 2003-07-12 14:49:00 | | 1020 | 103 | 1 | 2004-01-12 14:51:00 | +---------+--------+----------+---------------------+ 20 rows in set (0.00 sec) mysql> select * from Books; +--------+-----------+---------+ | BookID | BookName | InStock | +--------+-----------+---------+ | 1 | Poet | 1934 | | 2 | Ohio | 1919 | | 3 | Angels | 1966 | | 4 | Black | 1932 | | 101 | Writing | 7 | | 102 | News | 17 | | 103 | Angels | 23 | | 104 | Poet | 32 | | 105 | Dunces | 6 | | 106 | Solitude | 28 | | 107 | Postcards | 1992 | | 108 | The | 1993 | +--------+-----------+---------+ 12 rows in set (0.00 sec)

  • /

Drop table Books; Drop table Orders;

CREATE TABLE Books ( 

  BookID SMALLINT NOT NULL PRIMARY KEY,
  BookName VARCHAR(40) NOT NULL,
  InStock SMALLINT NOT NULL

) ENGINE=INNODB;

CREATE TABLE Orders ( 

  OrderID SMALLINT NOT NULL PRIMARY KEY,
  BookID SMALLINT NOT NULL,
  Quantity TINYINT (40) NOT NULL DEFAULT 1,
  DateOrdered TIMESTAMP,
  FOREIGN KEY (BookID) REFERENCES Books (BookID)

) ENGINE=INNODB;

INSERT INTO Orders VALUES (1001, 103, 1, "2004-01-12 12:30:00"), 

                         (1002, 101, 1, "2001-02-12 12:31:00"),
                         (1003, 103, 2, "2002-03-12 12:34:00"),
                         (1004, 104, 3, "2003-04-12 12:36:00"),
                         (1005, 102, 1, "2004-05-12 12:41:00"),
                         (1006, 103, 2, "2001-06-12 12:59:00"),
                         (1007, 101, 1, "2002-07-12 13:01:00"),
                         (1008, 103, 1, "2003-08-12 13:02:00"),
                         (1009, 102, 4, "2004-09-12 13:22:00"),
                         (1010, 101, 2, "2005-11-12 13:30:00"),
                         (1011, 103, 1, "2006-12-12 13:32:00"),
                         (1012, 105, 1, "2001-02-12 13:40:00"),
                         (1013, 106, 2, "2002-04-12 13:44:00"),
                         (1014, 103, 1, "2003-06-12 14:01:00"),
                         (1015, 106, 1, "2005-01-12 14:05:00"),
                         (1016, 104, 2, "2003-11-12 14:28:00"),
                         (1017, 105, 1, "2002-03-12 14:31:00"),
                         (1018, 102, 1, "2001-05-12 14:32:00"),
                         (1019, 106, 3, "2003-07-12 14:49:00"),
                         (1020, 103, 1, "2004-01-12 14:51:00");

INSERT INTO Books VALUES (101, "Writing", 12), 

                        (102, "News", 17),
                        (103, "Angels", 23),
                        (104, "Poet", 32),
                        (105, "Dunces", 6),
                        (106, "Solitude", 28);

select * from Orders; select * from Books; UPDATE Books, Orders SET Orders.Quantity=Orders.Quantity+2, 

  Books.InStock=Books.InStock-2

WHERE Books.BookID=Orders.BookID 

  AND Orders.OrderID = 1002;

select * from Orders; select * from Books; 


      </source>


Update with condition

   <source lang="sql">

/* mysql> select * from Bird; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | BlueBird | Joe | Car | f | 1999-03-30 | NULL | +----------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec) mysql> UPDATE Bird SET birth = "1989-08-31" WHERE name = "BlueBird"; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from Bird; +----------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+-------+---------+------+------------+-------+ | BlueBird | Joe | Car | f | 1989-08-31 | NULL | +----------+-------+---------+------+------------+-------+ 1 row in set (0.03 sec)

  • /

Drop table Bird; CREATE TABLE Bird ( 

   name VARCHAR(20), 
   owner VARCHAR(20),
   species VARCHAR(20), 
   sex CHAR(1), 
   birth DATE, 
   death DATE

);

INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);

select * from Bird; UPDATE Bird SET birth = "1989-08-31" WHERE name = "BlueBird";

select * from Bird; 

      </source>


Update with limitation and calculation

   <source lang="sql">

/* mysql> select * from Orders; +---------+--------+----------+---------------------+ | OrderID | BookID | Quantity | DateOrdered | +---------+--------+----------+---------------------+ | 1001 | 103 | 1 | 2004-01-12 12:30:00 | | 1002 | 101 | 1 | 2001-02-12 12:31:00 | | 1003 | 103 | 2 | 2002-03-12 12:34:00 | | 1004 | 104 | 3 | 2003-04-12 12:36:00 | | 1005 | 102 | 1 | 2004-05-12 12:41:00 | | 1006 | 103 | 2 | 2001-06-12 12:59:00 | | 1007 | 101 | 1 | 2002-07-12 13:01:00 | | 1008 | 103 | 1 | 2003-08-12 13:02:00 | | 1009 | 102 | 4 | 2004-09-12 13:22:00 | | 1010 | 101 | 2 | 2005-11-12 13:30:00 | | 1011 | 103 | 1 | 2006-12-12 13:32:00 | | 1012 | 105 | 1 | 2001-02-12 13:40:00 | | 1013 | 106 | 2 | 2002-04-12 13:44:00 | | 1014 | 103 | 1 | 2003-06-12 14:01:00 | | 1015 | 106 | 1 | 2005-01-12 14:05:00 | | 1016 | 104 | 2 | 2003-11-12 14:28:00 | | 1017 | 105 | 1 | 2002-03-12 14:31:00 | | 1018 | 102 | 1 | 2001-05-12 14:32:00 | | 1019 | 106 | 3 | 2003-07-12 14:49:00 | | 1020 | 103 | 1 | 2004-01-12 14:51:00 | +---------+--------+----------+---------------------+ 20 rows in set (0.01 sec) mysql> UPDATE Orders 

   -> SET Quantity=Quantity+1
   -> WHERE BookID=103
   -> ORDER BY DateOrdered DESC
   -> LIMIT 5;

Query OK, 5 rows affected (0.03 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from Orders; +---------+--------+----------+---------------------+ | OrderID | BookID | Quantity | DateOrdered | +---------+--------+----------+---------------------+ | 1001 | 103 | 2 | 2005-10-09 08:51:26 | | 1002 | 101 | 1 | 2001-02-12 12:31:00 | | 1003 | 103 | 2 | 2002-03-12 12:34:00 | | 1004 | 104 | 3 | 2003-04-12 12:36:00 | | 1005 | 102 | 1 | 2004-05-12 12:41:00 | | 1006 | 103 | 2 | 2001-06-12 12:59:00 | | 1007 | 101 | 1 | 2002-07-12 13:01:00 | | 1008 | 103 | 2 | 2005-10-09 08:51:26 | | 1009 | 102 | 4 | 2004-09-12 13:22:00 | | 1010 | 101 | 2 | 2005-11-12 13:30:00 | | 1011 | 103 | 2 | 2005-10-09 08:51:26 | | 1012 | 105 | 1 | 2001-02-12 13:40:00 | | 1013 | 106 | 2 | 2002-04-12 13:44:00 | | 1014 | 103 | 2 | 2005-10-09 08:51:26 | | 1015 | 106 | 1 | 2005-01-12 14:05:00 | | 1016 | 104 | 2 | 2003-11-12 14:28:00 | | 1017 | 105 | 1 | 2002-03-12 14:31:00 | | 1018 | 102 | 1 | 2001-05-12 14:32:00 | | 1019 | 106 | 3 | 2003-07-12 14:49:00 | | 1020 | 103 | 2 | 2005-10-09 08:51:26 | +---------+--------+----------+---------------------+ 20 rows in set (0.00 sec)

  • /

Drop table Orders; CREATE TABLE Orders ( 

  OrderID SMALLINT NOT NULL PRIMARY KEY,
  BookID SMALLINT NOT NULL,
  Quantity TINYINT (40) NOT NULL DEFAULT 1,
  DateOrdered TIMESTAMP,
  FOREIGN KEY (BookID) REFERENCES Books (BookID)

) ENGINE=INNODB;

INSERT INTO Orders VALUES (1001, 103, 1, "2004-01-12 12:30:00"), 

                         (1002, 101, 1, "2001-02-12 12:31:00"),
                         (1003, 103, 2, "2002-03-12 12:34:00"),
                         (1004, 104, 3, "2003-04-12 12:36:00"),
                         (1005, 102, 1, "2004-05-12 12:41:00"),
                         (1006, 103, 2, "2001-06-12 12:59:00"),
                         (1007, 101, 1, "2002-07-12 13:01:00"),
                         (1008, 103, 1, "2003-08-12 13:02:00"),
                         (1009, 102, 4, "2004-09-12 13:22:00"),
                         (1010, 101, 2, "2005-11-12 13:30:00"),
                         (1011, 103, 1, "2006-12-12 13:32:00"),
                         (1012, 105, 1, "2001-02-12 13:40:00"),
                         (1013, 106, 2, "2002-04-12 13:44:00"),
                         (1014, 103, 1, "2003-06-12 14:01:00"),
                         (1015, 106, 1, "2005-01-12 14:05:00"),
                         (1016, 104, 2, "2003-11-12 14:28:00"),
                         (1017, 105, 1, "2002-03-12 14:31:00"),
                         (1018, 102, 1, "2001-05-12 14:32:00"),
                         (1019, 106, 3, "2003-07-12 14:49:00"),
                         (1020, 103, 1, "2004-01-12 14:51:00");

select * from Orders; UPDATE Orders SET Quantity=Quantity+1 WHERE BookID=103 ORDER BY DateOrdered DESC LIMIT 5; select * from Orders; 


      </source>


Using UPDATE Statements to Modify Data in Joined Tables

   <source lang="sql">

Drop table DVDs; Drop table Studios;

CREATE TABLE DVDs ( 

  DVDID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  DVDName VARCHAR(60) NOT NULL,
  NumDisks TINYINT NOT NULL DEFAULT 1,
  YearRlsd YEAR NOT NULL,
  StudID VARCHAR(4) NOT NULL

) ENGINE=INNODB;

CREATE TABLE Studios ( 

  StudID VARCHAR(4) NOT NULL,
  StudDescrip VARCHAR(40) NOT NULL,
  PRIMARY KEY (StudID)

) ENGINE=INNODB; INSERT INTO Studios VALUES ("s101", "Universal Studios"), 

                          ("s102", "Warner Brothers"),
                          ("s103", "Time Warner"),
                          ("s104", "Columbia Pictures"),
                          ("s105", "Paramount Pictures"),
                          ("s106", "Twentieth Century Fox"),
                          ("s107", "Merchant Ivory Production");

INSERT INTO DVDs (DVDName, NumDisks, YearRlsd, StudID) VALUES 

    ("Christmas", 1, 2000, "s105"),
    ("What",      1, 2001, "s103"),
    ("Out",       1, 2000, "s101"),
    ("Falcon",    1, 2000, "s103"),
    ("Amadeus",   1, 1997, "s103"),
    ("Show",      2, 2000, "s106"),
    ("View",      1, 2000, "s107"),
    ("Mash",      2, 2001, "s106");

select * from DVDs; select * from Studios;

UPDATE DVDs, Studios SET DVDs.StutID="s2" WHERE DVDs.StudID=Studios.StudID 

  AND Studios.StudDescrip="Time Warner";

select * from DVDs; select * from Studios; 

      </source>
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Insert_Delete_Update/Update&oldid=5169»