MySQL Tutorial/Insert Update Delete/Update
Содержание
- 1 Change multiple columns using one statement
- 2 Syntac for UPDATE statement
- 3 Update a value based on the value it currently holds
- 4 UPDATE-ing Records
- 5 Update only 5 records
- 6 Update two tables in one update statement
- 7 Use the LIMIT function to control the number of rows that are affected by your UPDATE statement
Change multiple columns using one statement
<source lang="sql">
mysql> mysql> mysql> CREATE TABLE Employee(
-> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> );
Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.02 sec) mysql> mysql> mysql> UPDATE employee SET first_name = "Other Title",
-> salary = 9999 -> WHERE (ID = 1);
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> select * from employee; +------+-------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+-------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Other Title | Martin | 1996-07-25 | 2006-07-25 | 9999.00 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+-------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.02 sec) mysql> mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql></source>
Syntac for UPDATE statement
UPDATE tablename SET columnname = value WHERE x=y
<source lang="sql">
mysql> mysql> mysql> CREATE TABLE Employee(
-> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> );
Query OK, 0 rows affected (0.05 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.02 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> UPDATE Employee SET Last_Name = "Smith" WHERE ID = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Smith | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.02 sec) mysql></source>
Update a value based on the value it currently holds
<source lang="sql">
mysql> mysql> mysql> CREATE TABLE Employee(
-> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> );
Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.02 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.01 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.02 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> UPDATE Employee SET Salary = Salary + 1; Query OK, 8 rows affected (0.00 sec) Rows matched: 8 Changed: 8 Warnings: 0 mysql> mysql> select * from employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1235.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6662.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6545.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2345.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2335.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4323.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7898.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1233.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql></source>
UPDATE-ing Records
The syntax of the update statement.
<source lang="sql">
UPDATE <table_name> SET <column_name> = "new_value" WHERE (<column_name> = "some_value");</source>
Update only 5 records
<source lang="sql">
mysql> mysql> CREATE TABLE Books(
-> BookID SMALLINT NOT NULL PRIMARY KEY, -> BookName VARCHAR(40) NOT NULL, -> InStock SMALLINT NOT NULL -> ) -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec) mysql> mysql> 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;
Query OK, 0 rows affected (0.09 sec) mysql> mysql> mysql> INSERT INTO Books VALUES (101, "Java", 12),
-> (102, "PHP", 17), -> (103, "MySQL", 23), -> (104, "Perl", 32), -> (105, "Pyton", 6), -> (106, "www.sqle.ru", 28);
Query OK, 6 rows affected (0.03 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> mysql> INSERT INTO Orders VALUES (1001, 103, 1, "2001-11-12 12:30:00"),
-> (1002, 101, 1, "2002-10-16 12:31:00"), -> (1003, 103, 2, "2003-02-11 12:34:00"), -> (1004, 104, 3, "2004-01-19 12:36:00"), -> (1005, 102, 1, "2005-12-17 12:41:00"), -> (1006, 103, 2, "2006-10-18 12:59:00"), -> (1007, 101, 1, "2004-11-21 13:01:00"), -> (1008, 103, 1, "2014-10-16 13:02:00"), -> (1009, 102, 4, "1994-09-02 13:22:00"), -> (1010, 101, 2, "1995-10-04 13:30:00"), -> (1011, 103, 1, "1996-08-12 13:32:00"), -> (1012, 105, 1, "2004-10-03 13:40:00"), -> (1013, 106, 2, "2002-05-12 13:44:00"), -> (1014, 103, 1, "2001-10-01 14:01:00"), -> (1015, 106, 1, "1997-05-05 14:05:00"), -> (1016, 104, 2, "1998-10-07 14:28:00"), -> (1017, 105, 1, "2004-03-12 14:31:00"), -> (1018, 102, 1, "2004-10-21 14:32:00"), -> (1019, 106, 3, "1991-01-30 14:49:00"), -> (1020, 103, 1, "1990-10-12 14:51:00");
Query OK, 20 rows affected (0.05 sec) Records: 20 Duplicates: 0 Warnings: 0 mysql> mysql> select * from Books; +--------+----------------+---------+ | BookID | BookName | InStock | +--------+----------------+---------+ | 101 | Java | 12 | | 102 | PHP | 17 | | 103 | MySQL | 23 | | 104 | Perl | 32 | | 105 | Pyton | 6 | | 106 | www.sqle.ru | 28 | +--------+----------------+---------+ 6 rows in set (0.00 sec) mysql> mysql> select * from Orders; +---------+--------+----------+---------------------+ | OrderID | BookID | Quantity | DateOrdered | +---------+--------+----------+---------------------+ | 1001 | 103 | 1 | 2001-11-12 12:30:00 | | 1002 | 101 | 1 | 2002-10-16 12:31:00 | | 1003 | 103 | 2 | 2003-02-11 12:34:00 | | 1004 | 104 | 3 | 2004-01-19 12:36:00 | | 1005 | 102 | 1 | 2005-12-17 12:41:00 | | 1006 | 103 | 2 | 2006-10-18 12:59:00 | | 1007 | 101 | 1 | 2004-11-21 13:01:00 | | 1008 | 103 | 1 | 2014-10-16 13:02:00 | | 1009 | 102 | 4 | 1994-09-02 13:22:00 | | 1010 | 101 | 2 | 1995-10-04 13:30:00 | | 1011 | 103 | 1 | 1996-08-12 13:32:00 | | 1012 | 105 | 1 | 2004-10-03 13:40:00 | | 1013 | 106 | 2 | 2002-05-12 13:44:00 | | 1014 | 103 | 1 | 2001-10-01 14:01:00 | | 1015 | 106 | 1 | 1997-05-05 14:05:00 | | 1016 | 104 | 2 | 1998-10-07 14:28:00 | | 1017 | 105 | 1 | 2004-03-12 14:31:00 | | 1018 | 102 | 1 | 2004-10-21 14:32:00 | | 1019 | 106 | 3 | 1991-01-30 14:49:00 | | 1020 | 103 | 1 | 1990-10-12 14:51:00 | +---------+--------+----------+---------------------+ 20 rows in set (0.00 sec) mysql> mysql> mysql> 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> mysql> select * from Books; +--------+----------------+---------+ | BookID | BookName | InStock | +--------+----------------+---------+ | 101 | Java | 12 | | 102 | PHP | 17 | | 103 | MySQL | 23 | | 104 | Perl | 32 | | 105 | Pyton | 6 | | 106 | www.sqle.ru | 28 | +--------+----------------+---------+ 6 rows in set (0.00 sec) mysql> mysql> select * from Orders; +---------+--------+----------+---------------------+ | OrderID | BookID | Quantity | DateOrdered | +---------+--------+----------+---------------------+ | 1001 | 103 | 2 | 2007-07-23 19:09:37 | | 1002 | 101 | 1 | 2002-10-16 12:31:00 | | 1003 | 103 | 3 | 2007-07-23 19:09:37 | | 1004 | 104 | 3 | 2004-01-19 12:36:00 | | 1005 | 102 | 1 | 2005-12-17 12:41:00 | | 1006 | 103 | 3 | 2007-07-23 19:09:37 | | 1007 | 101 | 1 | 2004-11-21 13:01:00 | | 1008 | 103 | 2 | 2007-07-23 19:09:37 | | 1009 | 102 | 4 | 1994-09-02 13:22:00 | | 1010 | 101 | 2 | 1995-10-04 13:30:00 | | 1011 | 103 | 1 | 1996-08-12 13:32:00 | | 1012 | 105 | 1 | 2004-10-03 13:40:00 | | 1013 | 106 | 2 | 2002-05-12 13:44:00 | | 1014 | 103 | 2 | 2007-07-23 19:09:37 | | 1015 | 106 | 1 | 1997-05-05 14:05:00 | | 1016 | 104 | 2 | 1998-10-07 14:28:00 | | 1017 | 105 | 1 | 2004-03-12 14:31:00 | | 1018 | 102 | 1 | 2004-10-21 14:32:00 | | 1019 | 106 | 3 | 1991-01-30 14:49:00 | | 1020 | 103 | 1 | 1990-10-12 14:51:00 | +---------+--------+----------+---------------------+ 20 rows in set (0.00 sec) mysql> mysql> mysql> drop table Orders; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> drop table Books; Query OK, 0 rows affected (0.05 sec) mysql></source>
Update two tables in one update statement
<source lang="sql">
mysql> mysql> CREATE TABLE Books(
-> BookID SMALLINT NOT NULL PRIMARY KEY, -> BookName VARCHAR(40) NOT NULL, -> InStock SMALLINT NOT NULL -> ) -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec) mysql> mysql> 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;
Query OK, 0 rows affected (0.05 sec) mysql> mysql> mysql> INSERT INTO Books VALUES (101, "Java", 12),
-> (102, "PHP", 17), -> (103, "MySQL", 23), -> (104, "Perl", 32), -> (105, "Pyton", 6), -> (106, "www.sqle.ru", 28);
Query OK, 6 rows affected (0.05 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> mysql> INSERT INTO Orders VALUES (1001, 103, 1, "2001-11-12 12:30:00"),
-> (1002, 101, 1, "2002-10-16 12:31:00"), -> (1003, 103, 2, "2003-02-11 12:34:00"), -> (1004, 104, 3, "2004-01-19 12:36:00"), -> (1005, 102, 1, "2005-12-17 12:41:00"), -> (1006, 103, 2, "2006-10-18 12:59:00"), -> (1007, 101, 1, "2004-11-21 13:01:00"), -> (1008, 103, 1, "2014-10-16 13:02:00"), -> (1009, 102, 4, "1994-09-02 13:22:00"), -> (1010, 101, 2, "1995-10-04 13:30:00"), -> (1011, 103, 1, "1996-08-12 13:32:00"), -> (1012, 105, 1, "2004-10-03 13:40:00"), -> (1013, 106, 2, "2002-05-12 13:44:00"), -> (1014, 103, 1, "2001-10-01 14:01:00"), -> (1015, 106, 1, "1997-05-05 14:05:00"), -> (1016, 104, 2, "1998-10-07 14:28:00"), -> (1017, 105, 1, "2004-03-12 14:31:00"), -> (1018, 102, 1, "2004-10-21 14:32:00"), -> (1019, 106, 3, "1991-01-30 14:49:00"), -> (1020, 103, 1, "1990-10-12 14:51:00");
Query OK, 20 rows affected (0.05 sec) Records: 20 Duplicates: 0 Warnings: 0 mysql> mysql> select * from Books; +--------+----------------+---------+ | BookID | BookName | InStock | +--------+----------------+---------+ | 101 | Java | 12 | | 102 | PHP | 17 | | 103 | MySQL | 23 | | 104 | Perl | 32 | | 105 | Pyton | 6 | | 106 | www.sqle.ru | 28 | +--------+----------------+---------+ 6 rows in set (0.00 sec) mysql> mysql> select * from Orders; +---------+--------+----------+---------------------+ | OrderID | BookID | Quantity | DateOrdered | +---------+--------+----------+---------------------+ | 1001 | 103 | 1 | 2001-11-12 12:30:00 | | 1002 | 101 | 1 | 2002-10-16 12:31:00 | | 1003 | 103 | 2 | 2003-02-11 12:34:00 | | 1004 | 104 | 3 | 2004-01-19 12:36:00 | | 1005 | 102 | 1 | 2005-12-17 12:41:00 | | 1006 | 103 | 2 | 2006-10-18 12:59:00 | | 1007 | 101 | 1 | 2004-11-21 13:01:00 | | 1008 | 103 | 1 | 2014-10-16 13:02:00 | | 1009 | 102 | 4 | 1994-09-02 13:22:00 | | 1010 | 101 | 2 | 1995-10-04 13:30:00 | | 1011 | 103 | 1 | 1996-08-12 13:32:00 | | 1012 | 105 | 1 | 2004-10-03 13:40:00 | | 1013 | 106 | 2 | 2002-05-12 13:44:00 | | 1014 | 103 | 1 | 2001-10-01 14:01:00 | | 1015 | 106 | 1 | 1997-05-05 14:05:00 | | 1016 | 104 | 2 | 1998-10-07 14:28:00 | | 1017 | 105 | 1 | 2004-03-12 14:31:00 | | 1018 | 102 | 1 | 2004-10-21 14:32:00 | | 1019 | 106 | 3 | 1991-01-30 14:49:00 | | 1020 | 103 | 1 | 1990-10-12 14:51:00 | +---------+--------+----------+---------------------+ 20 rows in set (0.00 sec) mysql> mysql> 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.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> mysql> select * from Books; +--------+----------------+---------+ | BookID | BookName | InStock | +--------+----------------+---------+ | 101 | Java | 10 | | 102 | PHP | 17 | | 103 | MySQL | 23 | | 104 | Perl | 32 | | 105 | Pyton | 6 | | 106 | www.sqle.ru | 28 | +--------+----------------+---------+ 6 rows in set (0.00 sec) mysql> mysql> select * from Orders; +---------+--------+----------+---------------------+ | OrderID | BookID | Quantity | DateOrdered | +---------+--------+----------+---------------------+ | 1001 | 103 | 1 | 2001-11-12 12:30:00 | | 1002 | 101 | 3 | 2007-07-23 19:09:39 | | 1003 | 103 | 2 | 2003-02-11 12:34:00 | | 1004 | 104 | 3 | 2004-01-19 12:36:00 | | 1005 | 102 | 1 | 2005-12-17 12:41:00 | | 1006 | 103 | 2 | 2006-10-18 12:59:00 | | 1007 | 101 | 1 | 2004-11-21 13:01:00 | | 1008 | 103 | 1 | 2014-10-16 13:02:00 | | 1009 | 102 | 4 | 1994-09-02 13:22:00 | | 1010 | 101 | 2 | 1995-10-04 13:30:00 | | 1011 | 103 | 1 | 1996-08-12 13:32:00 | | 1012 | 105 | 1 | 2004-10-03 13:40:00 | | 1013 | 106 | 2 | 2002-05-12 13:44:00 | | 1014 | 103 | 1 | 2001-10-01 14:01:00 | | 1015 | 106 | 1 | 1997-05-05 14:05:00 | | 1016 | 104 | 2 | 1998-10-07 14:28:00 | | 1017 | 105 | 1 | 2004-03-12 14:31:00 | | 1018 | 102 | 1 | 2004-10-21 14:32:00 | | 1019 | 106 | 3 | 1991-01-30 14:49:00 | | 1020 | 103 | 1 | 1990-10-12 14:51:00 | +---------+--------+----------+---------------------+ 20 rows in set (0.02 sec) mysql> mysql> mysql> mysql> drop table Orders; Query OK, 0 rows affected (0.02 sec) mysql> mysql> drop table Books; Query OK, 0 rows affected (0.05 sec) mysql></source>
Use the LIMIT function to control the number of rows that are affected by your UPDATE statement
<source lang="sql">
mysql> mysql> mysql> CREATE TABLE Employee(
-> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> );
Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.02 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> UPDATE Employee SET Salary = Salary + 1 LIMIT 3; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> mysql> select * from employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1235.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6662.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6545.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> This statement would limit the update to the first 3 rows of the table. mysql> mysql> mysql> mysql></source>
