Mysql update is a complex update query. MySQL Reference Guide

Updating data in a database means changing values ​​in existing table records. In this case, it is possible to both change the values ​​of fields in a group of rows (even all rows of the table), and edit the value of a field in an individual row.

In SQL, you can change a record in a database table using the UPDATE command. In its most minimal form, the data update command looks like this:

UPDATE table SET field = value

Here, UPDATE– a command indicating that the request is to update data;

table– the name of the table in which changes will be made;

SET– a command followed by comma-separated fields with assigned values;

field– table field to which the change will be made;

meaning– a new value that will be entered into the field.

For example, if you need to set a field in all rows of a table to zero, you can run the following query:

UPDATE goods SET price = 0

In this case, the price field in absolutely all available rows of the table will take the value 0.

Changing one value

Changing the values ​​of all fields in a table is extremely rare. Most often it is necessary to change the value of a specific entry. To do this, at the end of the line with the UPDATE command, a WHERE directive will be added, which specifies a condition that determines which line the update operation should be performed on.

There is a table:

For example, we need to update the cost of a product with its value known to us num. To do this, run the following query:

UPDATE goods SET price = 150 WHERE num = 2

Now, before the operation of changing fields, a row will be selected that satisfies the condition num = 2. There is only one such row in the table. In this stock, the price will be changed to the value 150. As a result, we get a table with the changed price of the product.

Making changes to multiple lines with a selection condition

If you remember all the variety of conditions in the query, you can imagine how diverse the samples can be. Therefore, update queries can be executed either with one row, or with a group of rows, or with all rows of the table. It all depends on the task you are facing, as well as on which table rows you need to perform update operations on.

For example, we want to halve the price of all goods that currently cost 100 or more. Request:

UPDATE goods SET price = price / 2 WHERE price >= 100

Condition WHERE here contains a rule according to which only products with a price equal to or more than 100 will be selected, and those products with a price below 100 will not be affected by the request.

price = price / 2– the formula by which the new price of goods will be calculated. New price will be written equal to the old price divided by two.

As a result of executing such a query, we will obtain a table with changed records:

Updating values ​​in multiple row fields

If it is necessary to update several fields at once, all fields with their values ​​are indicated after the SET directive, separated by commas. For example, you need to change the name and price of a product with code 2 to “iron”, costing 300:

UPDATE goods SET title = "iron" , price = 300 WHERE num = 2 !}

This query will assign each matching field in a row its value. And the condition will indicate in which line the changes will be made.

The following are the main types of update operations. Based on them, queries are generated to solve most data modification problems in development using SQL.

If we need to change or update data in MySQL, we can use the SQL UPDATE command to work. ,

grammar

Following is the UPDATE command to change MySQL Sheet Data General SQL syntax:

UPDATE table_name SET field1=new-value1, field2=new-value2

• You can update one or more fields at the same time.
• You can specify any condition in the WHERE clause.
• You can also update data in a separate table.

When you need to update the data specified in the rows of a table, INEKE is very useful.

Command line to update data

Below we will update the w3big_tbl specified in the data table using the SQL UPDATE command:

examples

The following example will update the data table as w3big_title w3big_id field value 3:

# mysql -u root -p password; Enter password:******* mysql> use w3big; Database changed mysql> UPDATE w3big_tbl -> SET w3big_title="Learning JAVA" -> WHERE w3big_id=3; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> !}

Use PHP script to update data

PHP function to use mysql_query() to execute SQL statements, you can use the UPDATE SQL statement or INEKE does not apply.

This function in MySQL> command line the effect of executing SQL statements is the same.

examples

The following example will update the w3big_id data in the w3big_title 3 field.

UPDATE command— makes changes to an existing record or multiple records in a table SQL. Modifies existing values ​​in a table or the view's main table.

UPDATE Command Command Syntax

UPDATE Command Syntax

UPDATE command. Basic keywords and UPDATE command parameters

• schema - a permission identifier, usually the same as some username
• table view - table name SQL, in which the data is changed; if a view is defined, the data is modified in the main table SQL representation
• subquery_1 - subquery, which the server treats in the same way as a view
• Witholumn - table column SQL or submissions SQL, whose value changes; if the table column is from a sentence SET is omitted, the column value remains unchanged
• expr - ; this expression may contain main variables and optional indicator variables
• subquery_2 - new value assigned to the corresponding column
• subquery_3 - new value assigned to the corresponding column

WHERE- defines the range of rows to be modified by those for which a certain condition is TRUE; if this phrase is omitted, all rows in the table or view are modified.
When an approval is issued, any UPDATE trigger, defined on the table.
Subqueries. If the offer SET contains subquery, it returns exactly one row for each row modified. Each value in the subquery result is assigned to the corresponding list columns in parentheses. If the subquery does not return any rows, the column is assigned NULL. Subqueries can select data from the modified table. Offer SET can combine expressions and subqueries.

UPDATE Command Example 1
Changing the rating for all buyers to a value equal to 200:

Customers SET rating = 200;

UPDATE Command Example 2
Replacing a column value across all rows of a table is generally rarely used. Therefore in the team, as in the team DELETE, you can use a predicate. For execution specified replacement rating column values, for all customers served by the seller Giovanni (snum = 1003), you should enter:

Customers SET rating = 200 WHERE snum = 1001;

SQL UPDATE Command Example 3
In a sentence SET You can specify any number of values ​​for the columns, separated by commas:

Emp SET job = 'MANAGER', sal = sal + 1000, deptno = 20 WHERE ename = 'JONES';

UPDATE Command Example 4
In a sentence SET you can specify a NULL value without using any special syntax (such as IS NULL). Thus, if you want to set all customer ratings from London (city = 'London') to NULL, you would enter:

Customers SET rating = NULL WHERE city = 'London';

UPDATE Command Example 5
Explains the use of the following command syntax:

• Both sentence forms SET together in one statement.
• Subquery.
• A WHERE clause that limits the range of rows that can be modified.

Emp a SET deptno =
(SELECT deptno FROM dept WHERE loc = 'BOSTON'), (sal, comm) = ( SELECT 1.1*AVG(sal), 1.5*AVG(comm) FROM emp b WHERE a.deptno = b.deptno) WHERE deptno IN ( SELECT deptno FROM dept WHERE loc = 'DALLAS' OR loc = 'DETROIT');

The above statement does the following:

• Modifies only those employees who work in Dallas or Detroit
• Sets the value of the deptno column for employees from Boston
• Sets the salary of each employee at 1.1 times the average salary of the entire department
• Sets each employee's commission at 1.5 times the average commission for the entire department

In that textbook you will learn how to use MySQL UPDATE statement with syntax and examples.

Description

MySQL UPDATE statement used to update existing records in a table in a MySQL database. There are three syntaxes for the UPDATE statement, depending on the type of update you want to perform.

Syntax

A simple form of syntax for the UPDATE statement when updating a single table in MySQL:

Now the full syntax for the MySQL UPDATE statement when updating a single table is:

OR
The syntax for the UPDATE statement when updating one table with data from another table in MySQL is:

OR
The MySQL syntax for the UPDATE statement when updating multiple tables is:

Parameters or Arguments

LOW_PRIORITY - optional. If LOW_PRIORITY is specified, the update will be delayed until there are no processes reading from the table. LOW_PRIORITY can be used with MyISAM, MEMORY, and MERGE tables that use table-level locking.
IGNORE - optional. If IGNORE is provided, all errors encountered during the update are ignored. If updating on a row would break primary key or unique index, the update on this line will not be performed.
column1, column2 are the columns you want to update.
expression1 , expression2 — new values ​​for assigning column1 , column2 . So column1 is assigned the value of expression1 , column2 is assigned the value of expression2 , and so on.
WHERE conditions - optional. Conditions that must be met for the update to occur.
ORDER BY expression - optional. It can be used in conjunction with LIMIT to sort records appropriately while limiting the number of records to be updated.
LIMIT number_rows - optional. If LIMIT is specified, it controls the maximum number of records to update in the table. The maximum number of records specified in the number_rows file will be updated in the table.

Example updating one column

Let's look at a very simple example of a MySQL UPDATE query.

In this MySQL example, UPDATE updated the last_name field to 'Ford' in the customers table, where customer_id = 500.

Example of updating multiple columns

Let's look at the MySQL UPDATE example, where you can update more than one column using a single UPDATE statement.

If you want to update multiple columns, you can do so by separating the column/value pairs with commas.
This example MySQL UPDATE statement updated state to 'Nevada' and customer_rep to 23, where customer_id is greater than 200.

Example of updating a table with data from another table

Let's look at the UPDATE example, which shows how to update a table with data from another table in MySQL.

MySQL

This UPDATE example will update only the customers table for all records where customer_id is greater than 5000. When supplier_name from the suppliers table matches customer_name from the customers table, city from the suppliers table will be copied to the city field of the customers table.

This MySQL tutorial explains how to use the MySQL UPDATE statement with syntax and examples.

Syntax

In its simplest form, the syntax for the UPDATE statement when updating one table in MySQL is:

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

However, the full syntax for the MySQL UPDATE statement when updating one table is:

UPDATE [ LOW_PRIORITY ] [ IGNORE ] table SET column1 = expression1, column2 = expression2, ... ] ;

The syntax for the UPDATE statement when updating one table with data from another table in MySQL is:

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

The syntax for the MySQL UPDATE statement when updating multiple tables is:

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

Parameters or Arguments

LOW_PRIORITY Optional. If LOW_PRIORITY is provided, the update will be delayed until there are no processes reading from the table. LOW_PRIORITY may be used with MyISAM, MEMORY and MERGE tables that use table-level locking. IGNORE Optional. If IGNORE is provided, all errors encountered during the update are ignored. If an update on a row would result in a violation of a primary key or unique index, the update on that row is not performed. column1, column2 The columns that you wish to update. expression1, expression2, The new values ​​to assign to the expression1, expression2 column1, The new column2 . So expression1 would be assigned the value of expression2

, and so on.

WHERE conditions Optional. The conditions that must be met for the update to execute.

ORDER BY expression Optional. It may be used in combination with LIMIT to sort the records appropriately when limiting the number of records to be updated.

LIMIT number_rows Optional. If LIMIT is provided, it controls the maximum number of records to update in the table. At most, the number of records specified by number_rows will be update in the table. Example - Update single column Let's look at a very simple MySQL UPDATE query example. UPDATE customers SET last_name = "Anderson" WHERE customer_id = 5000; This MySQL UPDATE example would update the

last_name

to "Anderson" in the

customers

table where the

customer_id is 5000. Example - Update multiple columns Let's look at a MySQL UPDATE example where you might want to update more than one column with a single UPDATE statement. UPDATE customers SET last_name = "Anderson" WHERE customer_id = 5000; UPDATE customers SET state = "California", customer_rep = 32 WHERE customer_id > 100;

When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

state

to "California" and the
customer_rep
to 32 where the
is greater than 100.
Example - Update table with data from another table

Let's look at an UPDATE example that shows how to update a table with data from another table in MySQL. Example - Update single column UPDATE customers UPDATE customers SET last_name = "Anderson" WHERE customer_id = 5000; SET city = (SELECT city FROM suppliers WHERE suppliers.supplier_name = customers.customer_name) WHERE customer_id > 2000; This UPDATE example would update only the table for all records where the WHERE suppliers.supplier_name = customers.customer_name) Example - Update single column is greater than 2000. When the supplier_name from the supplier_name field in the Example - Update single column table.

Example - Update multiple Tables

Let's look at a MySQL UPDATE example where you might want to perform an update that involves more than one table in a single UPDATE statement.

UPDATE customers, suppliers SET customers.city = suppliers.city WHERE customers.customer_id = suppliers.supplier_id;

This MySQL UPDATE statement example would update the supplier_name field in the Example - Update single column table to the supplier_name WHERE suppliers.supplier_name = customers.customer_name) WHERE customer_id > 2000; Let's look at a very simple MySQL UPDATE query example. UPDATE customers SET last_name = "Anderson" WHERE customer_id = 5000; matches the supplier_id.