Mysql update syntax. SQL queries to update data (UPDATE)

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

grammar

Below is the UPDATE command to change MySQL data 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 w3big_tbl specified in the data table using SQL commands UPDATE INEKE:

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 SQL UPDATE statement or INEKE does not apply.

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

examples

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



The content of the article
1. The simplest MySQL queries
2. Simple SELECT queries
3. Simple INSERT (new entry) queries
4. Simple UPDATE (overwrite, append) queries
5. Simple DELETE (delete record) queries
6. Simple DROP (delete table) queries
7. Complex MySQL queries
8. MySQL queries and PHP variables

1. The simplest SQL queries

1. Displays a list of ALL databases.

SHOW databases;
2. Lists ALL tables in the base_name Database.

SHOW tables in base_name;

2. Simple SELECT queries to the MySQL database

SELECT– a query that selects existing data from the database. You can specify specific selection parameters for selection. For example, the essence of the request in Russian sounds like this: SELECT such and such columns FROM such and such table WHERE the parameter of such and such column is equal to the value.

1. Selects ALL data in the tbl_name table.

SELECT * FROM tbl_name;
2. Displays the number of records in the tbl_name table.

SELECT count(*) FROM tbl_name;
3. Selects (SELECT) from (FROM) table tbl_name limit (LIMIT) 3 records, starting from 2.

SELECT * FROM tbl_name LIMIT 2,3;
4. Selects (SELECT) ALL (*) records from (FROM) table tbl_name and sorts them (ORDER BY) by the id field in order.

SELECT * FROM tbl_name ORDER BY id;
5. Selects (SELECT) ALL records from (FROM) the tbl_name table and sorts them (ORDER BY) by the id field in REVERSE order.

SELECT * FROM tbl_name ORDER BY id DESC;
6. Selects ( SELECT) ALL (*) records from ( FROM) tables users and sorts them ( ORDER BY) on the field id in ascending order, limit ( LIMIT) first 5 entries.

SELECT * FROM users ORDER BY id LIMIT 5;
7. Selects all records from the table users, where is the field fname corresponds to the value Gena.

SELECT * FROM users WHERE fname="Gena";
8. Selects all records from the table users, where the field value fname begin with Ge.

SELECT * FROM users WHERE fname LIKE "Ge%";
9. Selects all records from the table users, Where fname ends with na, and sorts the records in ascending order of value id.

SELECT * FROM users WHERE fname LIKE "%na" ORDER BY id;
10. Selects all data from columns fname, lname from the table users.

SELECT fname, lname FROM users;

11. Let's say you have a country in your user data table. So, if you want to display ONLY a list of occurring values ​​(so that, for example, Russia is not displayed 20 times, but only once), then we use DISTINCT. It will bring Russia, Ukraine, Belarus out of the mass of repeating values. Thus, from the table users speakers country ALL UNIQUE values ​​will be output

SELECT DISTINCT country FROM users;
12. Selects ALL row data from the table users Where age has the values ​​18,19 and 21.

SELECT * FROM users WHERE age IN (18,19,21);
13. Selects MAXIMUM value age in the table users. That is, if you have the largest value in your table age(from English age) is 55, then the query result will be 55.

SELECT max(age) FROM users;
14. Select data from table users by fields name And age WHERE age takes the smallest value.

SELECT name, min(age) FROM users;
15. Select data from table users on the field name WHERE id NOT EQUAL TO 2.

SELECT name FROM users WHERE id!="2";

3. Simple INSERT (new entry) queries

INSERT– a query that allows you to INITIALLY insert a record into the database. That is, it creates a NEW record (line) in the database.

1. Does new entry in the table users, in field name inserts Sergey, and in the field age inserts 25. Thus, a new row with these values ​​is added to the table. If there are more columns, then the remaining ones will remain either empty or with default values.

INSERT INTO users (name, age) VALUES ("Sergey", "25");

4. Simple UPDATE queries to the MySQL database

UPDATE– a query that allows you to RESERVE field values ​​or ADD something to an already existing row in the database. For example, there is a ready-made line, but the age parameter needs to be rewritten in it, since it has changed over time.

1. In the table users age becomes 18.

UPDATE users SET age = "18" WHERE id = "3";
2. Everything is the same as in the first request, it just shows the request syntax, where two or more fields are overwritten.
In the table users WHERE id is equal to 3 field value age turns 18 and country Russia.

UPDATE users SET age = "18", country = "Russia" WHERE id = "3";

5. Simple DELETE (delete record) queries to the MySQL database

DELETE– a query that deletes a row from a table.

1. Removes a row from the table users WHERE id equals 10.

DELETE FROM users WHERE id = "10";

6. Simple DROP (delete table) queries to a MySQL database

DROP– a query that deletes a table.

1. Deletes the entire table tbl_name.

DROP TABLE tbl_name;

7. Complex queries to the MySQL database

Interesting queries that can be useful even for experienced users

SELECT id,name,country FROM users,admins WHERE TO_DAYS(NOW()) - TO_DAYS(registration_date)<= 14 AND activation != "0" ORDER BY registration_date DESC;
This complex query SELECTS columns id,name,country IN TABLES users,admins WHERE registration_date(date) not older 14 days I activation NOT EQUAL 0 , Sort by registration_date in reverse order (new first).

UPDATE users SET age = "18+" WHERE age = (SELECT age FROM users WHERE male = "man");
Above is an example of the so-called request within a request in SQL. Update age among users to 18+, where gender is male. I do not recommend such request options. From personal experience I will say that it is better to create several separate ones - they will be processed faster.

8. MySQL and PHP database queries

In MySQL queries in a PHP page, you can insert variables as compared values, etc. A couple of examples

1. Selects all records from the table users, where is the field fname corresponds to the value of the variable $name.

SELECT * FROM users WHERE fname="$name";
2. In the table users WHERE id is equal to 3 field value age changes to the value of the $age variable.

UPDATE users SET age = "$age" WHERE id = "3";

Attention! If you are interested in any other example, then write a question in the comments!

UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2, ...]

The UPDATE statement updates columns with their new values ​​in the rows of an existing table. The SET statement specifies which columns should be modified and what values ​​should be set in them. The WHERE clause, if present, specifies which rows are updated. Otherwise, all rows are updated. If an ORDER BY expression is specified, the rows will be updated in the order specified in it.

If indicated keyword LOW_PRIORITY , then execution of this UPDATE command is delayed until other clients have finished reading this table.

If the IGNORE keyword is specified, the update command will not abort even if the update encounters a duplicate key error. Rows that cause conflicts will not be updated.

If a column is accessed from the specified expression by the tbl_name argument, then the UPDATE command uses the current value for that column. For example, the following command sets the age column to a value one greater than its current value:

Mysql> UPDATE persondata SET age=age+1;

The UPDATE command assigns values ​​from left to right. For example, the following command duplicates the age column, then increments it:

Mysql> UPDATE persondata SET age=age*2, age=age+1;

If a column is set to its current value, then MySQL notices this and does not update it.

The UPDATE command returns the number of rows actually changed. In MySQL version 3.22 and later, the C API function mysql_info() returns the number of rows that were found and updated and the number of warnings that occurred when UPDATE was executed.

In MySQL version 3.23, you can use LIMIT # to ensure that only the specified number of rows have been modified.

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 column1, column2. So column1 expression1, column2 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 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 customer_id 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.

UPDATE customers
SET city = (SELECT city
FROM suppliers
WHERE suppliers.supplier_name = customers.customer_name)
WHERE customer_id > 2000;

This UPDATE example would update only the customers table for all records where the customer_id is greater than 2000. When the supplier_name from the suppliers table matches the customer_name from the customers table, the city from the suppliers table would be copied to the city field in the customers 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 city field in the customers table to the city from the suppliers table where the customer_id matches the supplier_id.

UPDATE syntax

Single table syntax:
UPDATE shya_tabletsh
SET column_name1=expression1 [, Name_ column2=expression2 ...]


Multi-table syntax:

UPDATE table_name [, table_name...] SET column_name 1=expression1 [,column_name2=expression2...]
The UPDATE statement updates the columns of existing table rows with new values. The SET clause lists the columns to be modified and the values ​​to be assigned to them. If the WHERE clause is specified, it specifies which rows should be updated. Otherwise, all rows in the table are updated. If the ORDER BY clause is specified, the rows will be updated in the specified order. The LIMIT construct imposes a limit on the number of rows to be updated.
The UPDATE statement supports the following modifiers:

  1. If the LOW_PRIORITY keyword is specified, the UPDATE is delayed until all other clients have finished reading the table.
  2. If the IGNORE keyword is specified, the update operation will not fail even if duplicate key errors occur. Rows that cause conflicts will not be updated.

If you are using columns from a table table_name in expressions, UPDATE uses the current value of the columns. For example, the following statement increments the age column by one:
mysql> UPDATE persondata SET age=age+l;
Assignments in UPDATE are made from left to right. For example, the following statement doubles the value of the age column and then increments it by one: mysql> UPDATE persondata SET age=age*2, age=age+l;
If you set the value of a column to whatever it is, MySQL detects this and does not update.
If you update a column that has been declared NOT null to NULL, it is set to the default value appropriate for the particular data type and increments the warning counter by one. The default value is 0 for numeric columns, the empty string ("") for character columns, and "null" for date and time columns.
UPDATE returns the number of rows that were actually updated. In MySQL 3.22 and later, the mysql_info() C API function API returns the number of rows that matched the query and were updated, and the number of warnings that occurred during the UPDATE.
As of MySQL 3.23, you can use limit number of lines to limit the scope of UPDATE.
The LIMIT construct works as follows:

  1. Prior to MySQL 4.0.13, LIMIT was a limit on the number of rows processed. The operator exited as soon as it updated number of lines rows that satisfied the WHERE condition.
  2. Since MySQL 4.0.13, limit is the string matching limit. The operator exits as soon as it finds number of lines rows that satisfy the WHERE condition, regardless of whether they were actually updated.

If the UPDATE statement includes an order by clause, then the rows are updated in the order specified by this clause. ORDER BY can be used starting from MySQL 4.0.0.
Since MySQL 4.0.0, it is also possible to perform UPDATE operations that operate on multiple tables at once:
UPDATE items,month SET items.price=month.price WHERE items.id-month. id/ This example demonstrates an inner join using the comma operator, but multi-table UPDATEs can use any join type allowed in SELECT statement, for example, LEFT JOIN.
On a note!

  • You cannot use ORDER BY or LIMIT in multi-table UPDATE statements.
Prior to MySQL 4.0.18, it was necessary to have UPDATE privilege on all tables used in a multi-table UPDATE, even if they were not actually updated. Starting with MySQL 4.0.18, such tables whose columns are only read but not updated only need to have the SELECT privilege.
If you use a multi-table UPDATE statement on InnoDB tables that have foreign key constraints defined, the MySQL optimizer may process them in a different order than that specified by their parent-child relationships. In this case, the statement will fail and the transaction will be rolled back. Instead, update a single table and rely on the ON UPDATE property, which provides the InnoDB engine for automatic update related tables.