MySQL - UPDATE

mysql

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.

Using UPDATE with ORDER BY and LIMIT:

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.

Multi-table UPDATE:

UPDATE supports a multi-table syntax that enables you to update a table using the content of another table. This syntax also allows multiple tables to be update simultaneously.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License