Mysql update query with example

MySQL UPDATE JOIN syntax

You often use joins to query rows from a table that have (in the case of ) or may not have (in the case of ) matching rows in another table. In MySQL, you can use the clauses in the statement to perform the cross-table update.


The syntax of the MySQL  is as follows:

Let’s examine the MySQL  syntax in greater detail:

  • First, specify the main table ( ) and the table that you want the main table to join to ( ) after the clause. Notice that you must specify at least one table after the  clause. The data in the table that is not specified after the  clause will not be updated.
  • Next, specify a kind of join you want to use i.e., either  or  and a join predicate. The clause must appear right after the clause.
  • Then, assign new values to the columns in and/or tables that you want to update.
  • After that, specify a condition in the clause to limit rows to rows for updating.

If you follow the statement tutorial, you will notice that there is another way to update data cross-table using the following syntax:

This  statement works the same as   with an implicit  clause. It means you can rewrite the above statement as follows:

Let’s take a look at some examples of using the  statement to having a better understanding.

Синтаксис

Простая форма синтаксиса для оператора UPDATE при обновлении одной таблицы в MySQL:

UPDATE table SET column1 = expression1, column2 = expression2, … ;

Теперь полный синтаксис для MySQL оператора UPDATE при обновлении одной таблицы:

UPDATE table SET column1 = expression1, column2 = expression2, … ] ;

ИЛИ Синтаксис оператора UPDATE при обновлении одной таблицы данными из другой таблицы в MySQL:

UPDATE table1 SET column1 = (SELECT expression1 FROM table2 WHERE conditions) ;

ИЛИ Синтаксис MySQL оператора UPDATE при обновлении нескольких таблиц:

UPDATE table1, table2, … SET column1 = expression1, column2 = expression2, … WHERE table1.column = table2.column AND conditions;

Параметры или аргументы

LOW_PRIORITY — необязательный. Если указан LOW_PRIORITY, обновление будет отложено до тех пор, пока не будет процессов читающих из таблицы. LOW_PRIORITY может использоваться с таблицами MyISAM, MEMORY и MERGE, которые используют блокировку на уровне таблицы.IGNORE — необязательный. Если предоставляется IGNORE, все ошибки, возникающие во время обновления, игнорируются. Если обновление в строке приведет к нарушению первичного ключа или уникального индекса, обновление в этой строке не будет выполнено.column1, column2 — столбцы, которые вы хотите обновить.expression1, expression2 — новые значения для назначения column1, column2. Таким образом, column1 присваивается значение expression1, column2 присваивается значение expression2 и т. д.WHERE conditions — необязательный. Условия, которые должны выполняться для выполнения обновления.ORDER BY expression — необязательный. Он может использоваться в сочетании с LIMIT для сортировки записей соответствующим образом при ограничении количества записей, подлежащих обновлению.LIMIT number_rows — необязательный. Если указан LIMIT, он контролирует максимальное количество записей для обновления в таблице. Максимальное количество записей, указанных в файле number_rows, будет обновляться в таблице.

MySQL INNER JOIN clause

The inner join clause joins two tables based on a condition which is known as a join predicate.

The inner join clause compares each row from the first table with every row from the second table. If values in both rows cause the join condition evaluates to true, the inner join clause creates a new row whose column contains all columns of the two rows from both tables and include this new row in the final result set. In other words, the inner join clause includes only rows whose values match.

The following shows the basic syntax of the inner join clause that joins two tables and :

If the join condition uses the equal operator () and the column names in both tables used for matching are the same, you can use the clause instead:

The following statement finds members who are also the committee members:

In this example, the inner join clause used the values in the columns in both tables and to match.

The following Venn diagram illustrates the inner join:

Because the name columns are the same in both tables, you can use the clause as shown in the following query:

7. Многократное обновление таблиц (с помощью Inner Join)

Можно также объединить две таблицы во время обновления. Можно также обновить значения из двух таблиц одновременно с помощью одного оператора обновления.

В этом примере мы будем использовать в таблице следующие преимущества наряду с нашей существующей таблицей сотрудников.

Следующее утверждение обновление будет увеличивать значение столбца заработной платы из таблицы сотрудников к значению, указанному в таблице льгот для соответствующего столбца DEPT.


Это означает, что мы должны объединить оба работника и таблицу преимущества во время обновления, как показано ниже. Используйте общее поле между этими двумя таблицами в предложении WHERE. В этом примере общее поле dept.

Кроме того, сразу после ключевого слова UPDATE укажите имя обоих таблиц, как показано ниже. После ключевого слова SET можно указать либо имя одного или нескольких столбцов, которые должны быть обновлены, либо из одной таблицы, или как в таблице.

Обратите внимание, что в приведенном выше обновлении, мы используем внутреннее соединение. Ниже приведен вывод после того, как приведенное выше утверждение обновление было выполнено

Как вы видите ниже, зарплата работника получила увеличивается на основе значений из таблицы выгоды

Ниже приведен вывод после того, как приведенное выше утверждение обновление было выполнено. Как вы видите ниже, зарплата работника получила увеличивается на основе значений из таблицы выгоды.

Select Data With MySQLi

The following example selects the id, firstname and lastname columns from the MyGuests table and displays it on the page:

Example (MySQLi Object-oriented)

<?php$servername = «localhost»;$username = «username»;$password = «password»;$dbname = «myDB»;// Create connection$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {   die(«Connection failed: » . $conn->connect_error); } $sql = «SELECT id, firstname, lastname FROM MyGuests»;$result = $conn->query($sql);if ($result->num_rows > 0) {  // output data of each row   while($row = $result->fetch_assoc()) {    echo «id: » . $row. » — Name: » . $row. » » . $row. «<br>»;   }} else {  echo «0 results»;} $conn->close(); ?>

Code lines to explain from the example above:

First, we set up an SQL query that selects the id, firstname and lastname columns from the MyGuests table. The next line of code runs the query and puts the resulting data into a variable called $result.

Then, the checks if there are more than zero rows returned.

If there are more than zero rows returned, the function puts all the results into an associative array that we can loop through. The loop loops through the result set and outputs the data from the id, firstname and lastname columns.

The following example shows the same as the example above, in the MySQLi procedural way:

Example (MySQLi Procedural)

<?php$servername = «localhost»;$username = «username»;$password = «password»;$dbname = «myDB»;// Create connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Check connection if (!$conn) {   die(«Connection failed: » . mysqli_connect_error());}$sql = «SELECT id, firstname, lastname FROM MyGuests»;$result = mysqli_query($conn, $sql);if (mysqli_num_rows($result) > 0) {   // output data of each row  while($row = mysqli_fetch_assoc($result)) {    echo «id: » . $row. » — Name: » . $row. » » . $row. «<br>»;   }} else {  echo «0 results»;}mysqli_close($conn); ?>

You can also put the result in an HTML table:

Example (MySQLi Object-oriented)

<?php$servername = «localhost»;$username = «username»;$password = «password»;$dbname = «myDB»;// Create connection$conn = new mysqli($servername, $username, $password, $dbname);// Check connectionif ($conn->connect_error) {   die(«Connection failed: » . $conn->connect_error); } $sql = «SELECT id, firstname, lastname FROM MyGuests»;$result = $conn->query($sql);if ($result->num_rows > 0) {   echo «<table><tr><th>ID</th><th>Name</th></tr>»;   // output data of each row  while($row = $result->fetch_assoc()) {    echo «<tr><td>».$row.»</td><td>».$row.» «.$row.»</td></tr>»;   }  echo «</table>»;} else {  echo «0 results»;} $conn->close(); ?>

MySQL UPDATE examples

Let’s practice the statement.

1) Using MySQL to modify values in a single column example

See the following table from the sample database.

MySQL issued the number of rows affected:

In this statement:

  • The clause specifies the row with employee number will be updated.
  • The clause sets the value of the column to the new email.

Third,  execute the statement again to verify the change:

2) Using MySQL to modify values in multiple columns

Let’s verify the changes:

3) Using MySQL to replace string example

4) Using MySQL to update rows returned by a statement example

You can supply the values for the clause from a statement that queries data from other tables.

For example, in the table, some customers do not have any sale representative. The value of the column is as follows:

We can take a sale representative and update for those customers.

To do this, we can select a random employee whose job title is from the   table and update it for the   table.

This query selects a random employee from the table whose job title is the .

To update the sales representative employee number  column in the table, we place the query above in the clause of the statement as follows:

If you query data from the   table, you will see that every customer has a sales representative. In other words, the following query returns no row.


In this tutorial, you have learned how to use MySQL statement to update data in a database table.

  • Was this tutorial helpful?

Пример с соединением таблиц

Следующий MySQL пример AND показывает, как условие AND может использоваться для соединения нескольких таблиц в операторе SELECT. Например:

MySQL

SELECT orders.order_id, suppliers.supplier_name FROM suppliers, orders WHERE suppliers.supplier_id = orders.supplier_id AND suppliers.supplier_name = ‘Apple’;

1 2 3 4

SELECTorders.order_id,suppliers.supplier_name

FROMsuppliers,orders

WHEREsuppliers.supplier_id=orders.supplier_id

ANDsuppliers.supplier_name=’Apple’;

Хотя приведенный запрос работает отлично, можно его записать следующим образом, используя соединение INNER JOIN. Например:

MySQL

SELECT orders.order_id, suppliers.supplier_name FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE suppliers.supplier_name = ‘Apple’;

1 2 3 4 5

SELECTorders.order_id,suppliers.supplier_name

FROMsuppliers

INNER JOINorders

ONsuppliers.supplier_id=orders.supplier_id

WHEREsuppliers.supplier_name=’Apple’;

Этот пример MySQL условия AND вернет все строки, где supplier_name — это ‘Apple’. И таблицы suppliers и orders объединенных на основании supplier_id. Вы заметите, что все поля имеют префикс имен таблиц (например: orders.order_id). Это необходимо для устранения какой-либо двусмысленности в отношении того, к какой области относятся ссылки; поскольку одно и то же имя поля может существовать как в таблице suppliers, так и в таблице orders.

В этом случае в результирующем наборе будут отображаться только поля order_id и supplier_name (как указано в первой части оператора SELECT).

Обновление данных в таблице MySQL с помощью mysqli и PDO

Инструкция UPDATE используется для обновления существующих записей в таблице:

Обратите внимание на предложение WHERE в синтаксисе обновления: Предложение WHERE указывает, какие записи или записи, которые должны быть обновлены. Если опустить предложение WHERE, все записи будут обновлены!. Чтобы узнать больше о SQL, пожалуйста, посетите наш учебник SQL

Чтобы узнать больше о SQL, пожалуйста, посетите наш учебник SQL.

Давайте посмотрим на таблицу «мигуестс»:

id firstname lastname email reg_date
1 html css 2019-10-22 14:26:15
2 html css 2019-10-23 10:22:30

Следующие примеры обновляют запись с :

Пример (mysqli объектно-ориентированный)

connect_error) <die(«Connection failed: » . $conn->connect_error);>

$sql = «UPDATE MyGuests SET lastname=’Doe’ WHERE > if ($conn->query($sql) === TRUE) <echo «Record updated successfully»;> else <echo «Error updating record: » . $conn->error;>

Пример (mysqli процедурный)


if (mysqli_query($conn, $sql)) <echo «Record updated successfully»;> else <echo «Error updating record: » . mysqli_error($conn);>

Пример (PDO)

$sql = «UPDATE MyGuests SET lastname=’Doe’ WHERE > // Prepare statement $stmt = $conn->prepare($sql);

// execute the query $stmt->execute();

// echo a message to say the UPDATE succeeded echo $stmt->rowCount() . » records UPDATED successfully»; >catch(PDOException $e) <echo $sql . «» . $e->getMessage(); >

После обновления записи таблица будет выглядеть так:

Examples: MySQL UPDATE on multiple tables

Here we have used two tables book_mast and purchase for the following example as sample table. We have shown some of the columns in the associated tables. Here are the tables below —

MySQL> SELECT book_id,book_name,pub_lang,book_price
    -> FROM book_mast;
+--------+------------------------------------+---------+----------+
| book_id| book_name                          | pub_lang|book_price|
+--------+------------------------------------+---------+----------+
| BK001  | Introduction to Electrodynamics    | English |     85.00| 
| BK002  | Understanding of Steel Construction| English |    105.50| 
| BK003  | Guide to Networking                | Hindi   |    200.00| 
| BK004  | Transfer  of Heat and Mass         | English |    250.00| 
| BK005  | Conceptual Physics                 | NULL    |    145.00| 
| BK006  | Fundamentals of Heat               | German  |    112.00| 
| BK007  | Advanced 3d Graphics               | Hindi   |     56.00| 
| BK008  | Human Anatomy                      | German  |     50.50| 
| BK009  | Mental Health Nursing              | English |    145.00| 
| BK010  | Fundamentals of Thermodynamics     | English |    225.00| 
| BK011  | The Experimental Analysis of Cat   | French  |     95.00| 
| BK012  | The Nature  of World               | English |     88.00| 
| BK013  | Environment a Sustainable Future   | German  |    100.00| 
| BK014  | Concepts in Health                 | NULL    |    180.00| 
| BK015  | Anatomy & Physiology               | Hindi   |    135.00| 
| BK016  | Networks and Telecommunications    | French  |     45.00| 
+--------+------------------------------------+---------+----------+
16 rows in set (0.00 sec)

and 

MySQL> SELECT book_id,pub_lang,purch_price,total_cost
    -> FROM purchase;
+---------+----------+-------------+------------+
| book_id | pub_lang | purch_price | total_cost |
+---------+----------+-------------+------------+
| BK001   | English  |       75.00 |    1125.00 | 
| BK004   | English  |       55.00 |     440.00 | 
| BK005   | NULL     |       20.00 |     400.00 | 
| BK004   | English  |       35.00 |     525.00 | 
| BK001   | English  |       25.00 |     200.00 | 
| BK003   | Hindi    |       45.00 |     900.00 | 
+---------+----------+-------------+------------+
6 rows in set (0.02 sec)

If we want to update the book_price of the table book_mast by an increment of 5% and also update the purch_price and total_cost of puchase table by an increment of 5%, and this increment will affect only those rows in both book_mast and purchase table, which publishing language is English and book_id matching in both the tables, we can write the following code —

After updating it is to be shown that, the highlighted rows have been effected in both the tables.

MySQL> SELECT book_id,book_name,pub_lang,book_price
    -> FROM book_mast;
+--------+------------------------------------+---------+----------+
| book_id| book_name                          | pub_lang|book_price|
+--------+------------------------------------+---------+----------+
| BK001  | Introduction to Electrodynamics    | English |     89.25| 
| BK002  | Understanding of Steel Construction| English |    105.50| 
| BK003  | Guide to Networking                | Hindi   |    200.00| 
| BK004  | Transfer  of Heat and Mass         | English |    262.50| 
| BK005  | Conceptual Physics                 | NULL    |    145.00| 
| BK006  | Fundamentals of Heat               | German  |    112.00| 
| BK007  | Advanced 3d Graphics               | Hindi   |     56.00| 
| BK008  | Human Anatomy                      | German  |     50.50| 
| BK009  | Mental Health Nursing              | English |    145.00| 
| BK010  | Fundamentals of Thermodynamics     | English |    225.00| 
| BK011  | The Experimental Analysis of Cat   | French  |     95.00| 
| BK012  | The Nature  of World               | English |     88.00| 
| BK013  | Environment a Sustainable Future   | German  |    100.00| 
| BK014  | Concepts in Health                 | NULL    |    180.00| 
| BK015  | Anatomy & Physiology               | Hindi   |    135.00| 
| BK016  | Networks and Telecommunications    | French  |     45.00| 
+--------+------------------------------------+---------+----------+
16 rows in set (0.01 sec)

and

MySQL> SELECT book_id,pub_lang,purch_price,total_cost
    -> FROM purchase;
+---------+----------+-------------+------------+
| book_id | pub_lang | purch_price | total_cost |
+---------+----------+-------------+------------+
| BK001   | English  |       78.75 |    1181.25 | 
| BK004   | English  |       57.75 |     462.00 | 
| BK005   | NULL     |       20.00 |     400.00 | 
| BK004   | English  |       36.75 |     551.25 | 
| BK001   | English  |       26.25 |     210.00 | 
| BK003   | Hindi    |       45.00 |     900.00 | 
+---------+----------+-------------+------------+
6 rows in set (0.08 sec)

MySQL: Update with Join Statement

Sample tables

Problem

If we want to update the aval1of table11 with the bval1 of table12 against the following condition —

1). the id of table11 and table13 must be matched, and

2). bval2 of table12 must be matched with the cval1 of table13 —

then the following code can be used.

Code:

Explanation

Output:

Previous: INSERT with LEFT JOINNext: MySQL Delete

UNION

Последнее обновление: 22.05.2018

Оператор UNION позволяет обединить две однотипных выборки. Эти выборки могут быть из разных таблиц или из одной и той же таблицы. Формальный синтаксис объединения:

SELECT_выражение1
UNION  SELECT_выражение2
 SELECT_выражениеN]

Например, пусть в базе данных будут две отдельные таблицы для клиентов банка (таблица Customers) и для сотрудников банка (таблица Employees):

CREATE TABLE Customers
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL,
    AccountSum DECIMAL
);
CREATE TABLE Employees
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL
);
 
INSERT INTO Customers(FirstName, LastName, AccountSum) 
VALUES
('Tom', 'Smith', 2000),
('Sam', 'Brown', 3000),
('Mark', 'Adams', 2500),
('Paul', 'Ins', 4200),
('John', 'Smith', 2800),
('Tim', 'Cook', 2800);
 
INSERT INTO Employees(FirstName, LastName)
VALUES
('Homer', 'Simpson'),
('Tom', 'Smith'),
('Mark', 'Adams'),
('Nick', 'Svensson');

Здесь мы можем заметить, что обе таблицы, несмотря на наличие различных данных, могут характеризоваться двумя общими атрибутами — именем (FirstName) и фамилией (LastName). Выберем сразу всех клиентов банка и его сотрудников из обеих таблиц:

SELECT FirstName, LastName 
FROM Customers
UNION SELECT FirstName, LastName FROM Employees;

Здесь из первой таблицы выбираются два значения — имя и фамилия клиента. Из второй таблицы Employees также выбираются два значения — имя и фамилия сотрудников. То есть при объединении количество выбираемых столбцов и их тип совпадают для обеих выборок.

При этом названия столбцов объединенной выборки будут совпадать с названия столбцов первой выборки. И если мы захотим при этом еще произвести сортировку, то в выражениях ORDER BY необходимо ориентироваться именно на названия столбцов первой выборки:

SELECT FirstName AS FName, LastName
FROM Customers
UNION SELECT FirstName, LastName
FROM Employees
ORDER BY FName DESC;

В данном случае каждая выборка имеет по столбцу FName из первой выборки. Тем не менее при сортировке будет учитываться и значение столбца FirstName из второй выборки:

Если же в одной выборке больше столбцов, чем в другой, то они не смогут быть объединены. Например, в следующем случае объединение завершится с ошибкой:

SELECT FirstName, LastName, AccountSum
FROM Customers
UNION SELECT FirstName, LastName 
FROM Employees;

Если оба объединяемых набора содержат в строках идентичные значения, то при объединении повторяющиеся строки удаляются. Например, в случае с таблицами Customers и Employees сотрудники банка могут быть одновременно его клиентами и содержаться в обеих таблицах. При объединении в примерах выше всех дублирующиеся строки удалялись. Если же необходимо при объединении сохранить все, в том числе повторяющиеся строки, то для этого необходимо использовать оператор ALL:

SELECT FirstName, LastName
FROM Customers
UNION ALL SELECT FirstName, LastName 
FROM Employees
ORDER BY FirstName;

Объединять выборки можно и из одной и той же таблицы. Например, в зависимости от суммы на счете клиента нам надо начислять ему определенные проценты:

SELECT FirstName, LastName, AccountSum + AccountSum * 0.1 AS TotalSum 
FROM Customers WHERE AccountSum < 3000
UNION SELECT FirstName, LastName, AccountSum + AccountSum * 0.3 AS TotalSum 
FROM Customers WHERE AccountSum >= 3000;

В данном случае если сумма меньше 3000, то начисляются проценты в размере 10% от суммы на счете. Если на счете больше 3000, то проценты увеличиваются до 30%.

НазадВперед

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии


С этим читают