首页 > MySQL :: MySQL 8.0 Reference Manual :: 13.2.13 UPDATE Statement

update,MySQL :: MySQL 8.0 Reference Manual :: 13.2.13 UPDATE Statement

互联网 2021-01-22 05:16:23
在线算命,八字测算命理 13.2.13 UPDATE Statement

UPDATE is a DML statement thatmodifies rows in a table.

An UPDATE statement can start witha WITH clause to define commontable expressions accessible within theUPDATE. See Section 13.2.15, “WITH (Common Table Expressions)”.

Single-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_referenceSET assignment_list[WHERE where_condition][ORDER BY ...][LIMIT row_count]value:{expr | DEFAULT}assignment:col_name = valueassignment_list:assignment [, assignment] ...

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_referencesSET assignment_list[WHERE where_condition]

For the single-table syntax, theUPDATE statement updates columns ofexisting rows in the named table with new values. TheSET clause indicates which columns to modifyand the values they should be given. Each value can be given as anexpression, or the keyword DEFAULT to set acolumn explicitly to its default value. TheWHERE clause, if given, specifies theconditions that identify which rows to update. With noWHERE clause, all rows are updated. If theORDER BY clause is specified, the rows areupdated in the order that is specified. TheLIMIT clause places a limit on the number ofrows that can be updated.

For the multiple-table syntax,UPDATE updates rows in each tablenamed in table_references that satisfythe conditions. Each matching row is updated once, even if itmatches the conditions multiple times. For multiple-table syntax,ORDER BY and LIMIT cannot beused.

For partitioned tables, both the single-single and multiple-tableforms of this statement support the use of aPARTITION option as part of a table reference.This option takes a list of one or more partitions orsubpartitions (or both). Only the partitions (or subpartitions)listed are checked for matches, and a row that is not in any ofthese partitions or subpartitions is not updated, whether itsatisfies the where_condition or not.

Note

Unlike the case when using PARTITION with anINSERT orREPLACE statement, an otherwisevalid UPDATE ... PARTITION statement isconsidered successful even if no rows in the listed partitions(or subpartitions) match thewhere_condition.

For more information and examples, seeSection 24.5, “Partition Selection”.

where_condition is an expression thatevaluates to true for each row to be updated. For expressionsyntax, see Section 9.5, “Expressions”.

table_references andwhere_condition are specified asdescribed in Section 13.2.10, “SELECT Statement”.

You need the UPDATE privilege onlyfor columns referenced in an UPDATEthat are actually updated. You need only theSELECT privilege for any columnsthat are read but not modified.

The UPDATE statement supports thefollowing modifiers:

With the LOW_PRIORITY modifier, executionof the UPDATE is delayed untilno other clients are reading from the table. This affects onlystorage engines that use only table-level locking (such asMyISAM, MEMORY, andMERGE).

With the IGNORE modifier, the updatestatement does not abort even if errors occur during theupdate. Rows for which duplicate-key conflicts occur on aunique key value are not updated. Rows updated to values thatwould cause data conversion errors are updated to the closestvalid values instead. For more information, seeThe Effect of IGNORE on Statement Execution.

UPDATE IGNOREstatements, including those having an ORDER BYclause, are flagged as unsafe for statement-based replication.(This is because the order in which the rows are updateddetermines which rows are ignored.) Such statements produce awarning in the error log when using statement-based mode and arewritten to the binary log using the row-based format when usingMIXED mode. (Bug #11758262, Bug #50439) SeeSection 17.2.1.3, “Determination of Safe and Unsafe Statements in Binary Logging”, for moreinformation.

If you access a column from the table to be updated in anexpression, UPDATE uses the currentvalue of the column. For example, the following statement setscol1 to one more than its current value:

UPDATE t1 SET col1 = col1 + 1;

The second assignment in the following statement setscol2 to the current (updated)col1 value, not the originalcol1 value. The result is thatcol1 and col2 have the samevalue. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments aregenerally evaluated from left to right. For multiple-tableupdates, there is no guarantee that assignments are carried out inany particular order.

If you set a column to the value it currently has, MySQL noticesthis and does not update it.

If you update a column that has been declared NOTNULL by setting to NULL, an erroroccurs if strict SQL mode is enabled; otherwise, the column is setto the implicit default value for the column data type and thewarning count is incremented. The implicit default value is0 for numeric types, the empty string('') for string types, and the“zero” value for date and time types. SeeSection 11.6, “Data Type Default Values”.

If a generated column is updated explicitly, the only permittedvalue is DEFAULT. For information aboutgenerated columns, seeSection 13.1.20.8, “CREATE TABLE and Generated Columns”.

UPDATE returns the number of rowsthat were actually changed. Themysql_info() C API functionreturns the number of rows that were matched and updated and thenumber of warnings that occurred during theUPDATE.

You can use LIMITrow_count to restrict thescope of the UPDATE. ALIMIT clause is a rows-matched restriction. Thestatement stops as soon as it has foundrow_count rows that satisfy theWHERE clause, whether or not they actually werechanged.

If an UPDATE statement includes anORDER BY clause, the rows are updated in theorder specified by the clause. This can be useful in certainsituations that might otherwise result in an error. Suppose that atable t contains a column idthat has a unique index. The following statement could fail with aduplicate-key error, depending on the order in which rows areupdated:

UPDATE t SET id = id + 1;

For example, if the table contains 1 and 2 in theid column and 1 is updated to 2 before 2 isupdated to 3, an error occurs. To avoid this problem, add anORDER BY clause to cause the rows with largerid values to be updated before those withsmaller values:

UPDATE t SET id = id + 1 ORDER BY id DESC;

You can also perform UPDATEoperations covering multiple tables. However, you cannot useORDER BY or LIMIT with amultiple-table UPDATE. Thetable_references clause lists thetables involved in the join. Its syntax is described inSection 13.2.10.2, “JOIN Clause”. Here is an example:

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

The preceding example shows an inner join that uses the commaoperator, but multiple-table UPDATEstatements can use any type of join permitted inSELECT statements, such asLEFT JOIN.

If you use a multiple-table UPDATEstatement involving InnoDB tables for whichthere are foreign key constraints, the MySQL optimizer mightprocess tables in an order that differs from that of theirparent/child relationship. In this case, the statement fails androlls back. Instead, update a single table and rely on theON UPDATE capabilities thatInnoDB provides to cause the other tables to bemodified accordingly. SeeSection 13.1.20.5, “FOREIGN KEY Constraints”.

You cannot update a table and select directly from the same tablein a subquery. You can work around this by using a multi-tableupdate in which one of the tables is derived from the table thatyou actually wish to update, and referring to the derived tableusing an alias. Suppose you wish to update a table nameditems which is defined using the statementshown here:

CREATE TABLE items (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,quantity BIGINT NOT NULL DEFAULT 0);

To reduce the retail price of any items for which the markup is30% or greater and of which you have fewer than one hundred instock, you might try to use an UPDATE statementsuch as the one following, which uses a subquery in theWHERE clause. As shown here, this statementdoes not work:

mysql> UPDATE items > SET retail = retail * 0.9 > WHERE id IN > (SELECT id FROM items > WHERE retail / wholesale >= 1.3 AND quantity > 100);ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

Instead, you can employ a multi-table update in which the subqueryis moved into the list of tables to be updated, using an alias toreference it in the outermost WHERE clause,like this:

UPDATE items, (SELECT id FROM itemsWHERE id IN(SELECT id FROM items WHERE retail / wholesale >= 1.3 AND quantity < 100))AS discountedSET items.retail = items.retail * 0.9WHERE items.id = discounted.id;

Because the optimizer tries by default to merge the derived tablediscounted into the outermost query block, thisworks only if you force materialization of the derived table. Youcan do this by setting thederived_merge flag of theoptimizer_switch system variableto off before running the update, or by usingthe NO_MERGE optimizer hint, asshown here:

UPDATE /*+ NO_MERGE(discounted) */ items, (SELECT id FROM itemsWHERE retail / wholesale >= 1.3 AND quantity < 100)AS discountedSET items.retail = items.retail * 0.9WHERE items.id = discounted.id;

The advantage of using the optimizer hint in such a case is thatit applies only within the query block where it is used, so thatit is not necessary to change the value ofoptimizer_switch again after executing theUPDATE.

Another possibility is to rewrite the subquery so that it does notuse IN or EXISTS, like this:

UPDATE items, (SELECT id, retail / wholesale AS markup, quantity FROM items) AS discountedSET items.retail = items.retail * 0.9WHERE discounted.markup >= 1.3AND discounted.quantity < 100AND items.id = discounted.id;

In this case, the subquery is materialized by default rather thanmerged, so it is not necessary to disable merging of the derivedtable.

免责声明:非本网注明原创的信息,皆为程序自动获取自互联网,目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责;如此页面有侵犯到您的权益,请给站长发送邮件,并提供相关证明(版权证明、身份证正反面、侵权链接),站长将在收到邮件24小时内删除。