How to use update statement in mysql

Usage of update statement in mysql: [UPDATE table_name SET field1=new-value1 [WHERE Clause]]. The update statement is used to modify or update data in mysql.

update syntax

(Recommended tutorial: mysql video tutorial)

Single-table syntax :

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

UPDATE syntax can update each column in the original table row with a new value. The SET clause indicates which columns are to be modified and which values ​​are to be given. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If the ORDER BY clause is specified, rows are updated in the order specified. The LIMIT clause is used to give a limit to the number of rows that can be updated.

If you access a column via tbl_name in an expression, UPDATE uses the current value in the column. For example, the following statement sets the age column to one more than the current value:

UPDATE persondata SET age=age+1;

UPDATE assignments are evaluated from left to right. For example, the following statement doubles and then increments the age column:

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

If you set a column to the value it currently contains, MySQL will notice this but will not update it.

Some fields in the update table are null

update person set number=null,name=null;

If you update a column that has been defined as NOT NULL to NULL, the column is set to the default value corresponding to the column type. And the number of warnings is accumulated. For numeric types, the default value is 0; for string types, the default value is the empty string (''); for date and time types, the default value is the "zero" value.

UPDATE operations on multiple tables

UPDATE items,month SET items.price=month.price WHERE items.id=month.id;

The above example shows an inner union using the comma operator, but the multiple-table UPDATE statement can use any type allowed in the SELECT statement Unions, such as LEFT JOIN, but you cannot use ORDER BY or LIMIT at the same time as multiple-table UPDATE.

The above is the detailed content of How to use update statement in mysql. For more information, please follow other related articles on the PHP Chinese website!