Update mysql with update syntax

Let's talk about updating mysql using update syntax. The secret of the text is that it is relevant to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article updating mysql with update grammar.

Update syntax

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]

The UPDATE syntax can update columns in existing table rows with new values. The SET clause indicates which columns to modify and which values to give. 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, the 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 through 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

The UPDATE assignment is evaluated from left to right. For example, the following statement doubles the age column and then increases it:

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

If you set a column to its current value, MySQL will notice this, but will not update it.

Some fields of 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 accumulates the number of warnings. The default value is 0 for numeric types, empty string ('') for string types, and "zero" for date and time types.

UPDATE operations for multiple tables

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

The above example shows an internal union using a comma operator, but multiple-table UPDATE statements can use any type of union allowed in SELECT statements, such as LEFT JOIN, but you cannot use ORDER BY or LIMIT with multiple-table UPDATE.

For the above use of update syntax to update mysql related content, is there anything you don't understand? Or if you want to know more about it, you can continue to follow our industry information section.