MySQL - UPDATE

mysql

https://dba.stackexchange.com/questions/119621/how-to-update-10-million-rows-in-mysql-single-table-as-fast-as-possible
https://dba.stackexchange.com/questions/28282/whats-the-most-efficient-way-to-batch-update-queries-in-mysql
https://stackoverflow.com/questions/6286452/mysql-bulk-insert-or-update
https://stackoverflow.com/questions/3432/multiple-updates-in-mysql
https://stackoverflow.com/questions/35726910/bulk-update-mysql-with-where-statement
https://www.electricmonk.nl/log/2013/11/06/increasing-performance-of-bulk-updates-of-large-tables-in-mysql/
http://www.databasejournal.com/features/mysql/article.php/3894641/Five-Handy-Tips-for-MySQLs-Powerful-UPDATE-Statement.htm
http://blog.bubble.ro/how-to-make-multiple-updates-using-a-single-query-in-mysql/

How can we use the UPDATE statement?

UPDATE customer SET name='Alex', email='alex@fudd.com' WHERE id=1005;
UPDATE tableName SET credit=credit - !payment!;

When the new value equals to existing value, MySQL ignores the assignment.

UPDATE reports a row-affected count to indicate how many rows were actually changed. This count doesn't include rows that were selected for updating but for which the update didn't change any columns from their current values.

If a table contains a TIMESTAMP column that has ON UPDATE CURRENT_TIMESTAMP in its definition, that column is updated automatically only if another column changes value. An UPDATE that sets columns to their current values does not change the TIMESTAMP. If you need the TIMESTAMP to be updated for every UPDATE, you can set it explicitly to the value of the CURRENT_TIMESTAMP function.

Some client programs or APIs enable you to ask the MySQL server to return a rows-matched count rather than rows-affected count. This causes the row count to include all rows selected for updating, even if their columns weren't changed from their existing values.

How can we control the order of which rows are updated?

To control the order of which rows are updated first (to avoid possible duplicate key violation):

UPDATE people SET id = id - 1 ORDER BY id;

UPDATE also allows a LIMIT clause, which places a limit on the number of records updated. ORDER BY and LIMIT may be used together in the same UPDATE statement.

How can we update columns in one table using values from another table?

update $chld_table as A ,$this->table_name as B set A.$fld = B.$fld where A.relation_id = B.contact_id and B.active =1;

update emailActivity_team ea,
  (
    select activityID, group_concat(contactID) as contactIDs
    from emailContactStatus ecs
    where ecs.status = 'draft'
    group by activityID
  ) as t1
  set ea.contactIDs = t1.contactIDs
  where ea.activityID = t1.activityID;

Normally, and update statement consist of only one table. This SQL consists of two tables. The second table is created by a sub-select statement. This SQL also join the two tables and update the first table using values from second table. The more general form:

UPDATE t1, t2 SET t1.name = t2.name WHERE t1.id = t2.id;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License