6.4.5 UPDATE Syntax

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

or

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

UPDATE updates columns in existing table rows with new values. The SET clause indicates which columns to modify and the values they should be given. The WHERE clause, if given, specifies which rows should be updated. Otherwise, all rows are updated. If the ORDER BY clause is specified, the rows will be updated in the order that is specified.

If you specify the keyword LOW_PRIORITY, execution of the UPDATE is delayed until no other clients are reading from the table.

If you specify the keyword IGNORE, the update statement will not abort even if we get duplicate key errors during the update. Rows that would cause conflicts will not be updated.

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

mysql> UPDATE persondata SET age=age+1;

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

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

If you set a column to the value it currently has, MySQL notices this and doesn't update it.

UPDATE returns the number of rows that were actually changed. In MySQL Version 3.22 or later, the C API function mysql_info() returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE. If you update a column that has been declared NOT NULL by setting to NULL, the column is set to the default value appropriate for the column type and the warning count is incremented. The default value is is 0 for numeric types, the empty string ('') for string types, and the ``zero'' value for date and time types.

Starting from MySQL version 3.23, you can use LIMIT row_count to ensure that only a given number of rows are changed. MySQL will stop the update as soon as it has found LIMIT rows that satisfies the WHERE clause, independent of the rows changed content or not.

If an ORDER BY clause is used (available from MySQL 4.0.0), the rows will be updated in that order. This is really only useful in conjunction with LIMIT.

Starting with MySQL Version 4.0.4, you can also perform UPDATE operations that cover multiple tables:

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

Note: you can not use ORDER BY or LIMIT with multi-table UPDATE.

User Comments

Word of warning concerning multu-table updates. If there is a 1-to-Many relationship between the tables, only the FIRST MATCH will be processed, so:-

TableA
ID SubTable Total
1 1 0.00
2 1 0.00
3 2 0.00

TableB
SubTable Sequence Value
1 1 5.00
1 2 10.00
2 1 12.00

UPDATE TableA, TableB SET Total = Total + Value
WHERE TableA.SubTable = TableB.SubTable;

Yields
ID SubTable Total
1 1 5.00
2 1 5.00
3 2 12.00

Not
ID SubTable Total
1 1 15.00
2 1 15.00
3 2 12.00

It migth be obvious for SQL jedis out there, but for the padawan I bet is good information to know that you can use the CONCAT argument in the UPDATE sintax, just like below:

UPDATE table SET field= CONCAT('tmp_', field);

It took me some time to figure it out.

When I was using "UPDATE" with two large tables, there is an error message occured - "table [table name] is full".

Eventhough I had changed system variables, like "BIG_TABLES", "MAX_JOIN_SIZE", "TMP_TABLE_SIZE", "MAX_ROWS", all above changes don't work at all.

for your reference, I am using MYSQL ver. 4.0.12, platform:
WINNT 4.0 SP6, Windows 2000, Redhat Linux 8.0.

The two tables contain more than 200,000 rows and the physical size less than 129MB.

For others like me still on version 3.*, please note that, if you have a table with a unique or primary key on a numeric column, some or all of the values of which you wish to increment or decrement, you don't need an ORDER BY clause .. I kind of panicked thinking that (without an ORDER BY clause) this would cause an error:

UPDATE tk_steps SET order_num = order_num + 1

.. depending on which way round MySQL worked on the rows. But it didn't - the system handles the issue transparently.

The multiple-table update problem mentioned above with very large tables is a bug in 4.0.12. Consider comments in documentation 'table is full error' and bug reports.
The problem shall be fixed in 4.0.13

If you try to update a field - for example a CHAR(x) - that is NOT NULL and you set it = NULL the field becomes '' (an empy string).

Hi
i used "DELETE FROM books WHERE id = 1 ORDER BY timestamp LIMIT 500" on a linux-system with 3.22-4a-beta (remote with JDBC).
Worked fine. ( I did not check if its reakky ordered...)
But not on Windows 2K With the same version ... :confused:

> For others like me still on version 3.*, please note that,
> if you have a table with a unique or primary key on a
> numeric column, some or all of the values of which you
> wish to increment or decrement, you don't need an ORDER BY
> clause .. I kind of panicked thinking that (without an
> ORDER BY clause) this would cause an error:
>
> UPDATE tk_steps SET order_num = order_num + 1

> .. depending on which way round MySQL worked on the rows.
> But it didn't - the system handles the issue
> transparently.

No, you are wrong. For a longer thread about this issue, see http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=bbop7d%242s4l%241%40FreeBSD.csie.NCTU.edu.tw&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26selm%3Dbbop7d%25242s4l%25241%2540FreeBSD.csie.NCTU.edu.tw

I suggest introducing UPDATE DELAYED statements analogous to INSERT DELAYED so that the client doesn't have to wait till the update is completed.

Hi guys,

I want to get the number of rows that an UPDATE statement actaully updated.

I cannot use the C API because the server that hosts my website doesn't have it. I do it with VB ASP.

I tried: "SELECT (UPDATE table_name SET variable_name = value_name WHERE field_name = a_value) in PHPMyAdmin, but it didn't work.

I need something like: LAST_INSERT_ID() but does what the mysql_info() of the C API does.

Thanks,
Yiannis

To Gabriele Scaroni who posted above:
If the column is declared as NOT NULL, there is a default value (which depends on the column type). For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value.

See: http://www.mysql.com/doc/en/CREATE_TABLE.html

and search for "A DEFAULT value" on the page to get the bullet which explains this. MyISAM tables (default table types in MySQL) don't offer much in the way of constraint handling and so the approach MySQL uses is to be as quiet as possible and let things like this slide since you can easily handle it in the application logic. Hence, the meaning of NOT NULL is more like "there must be a value for this column, so if one is not supplied we'll use a default". You can specifiy the DEFAULT value for a column if it is constnat (i.e., not a function such as PASSWORD()). You can also check for NULL conditions using IS NULL, IS NOT NULL, and the IFNULL() function.

In response to Yiannis Costopoulos, you may get the number of rows that have been affected by the UPDATE statement using the mysql_affected_rows() function after you perform the operation. I'm fairly sure this will work in your situation..

How can you update a column from a another table (in another database) where there exists a releationship between the the tables?

Something like:

update db1.table set db1.table.id = db2.table.id where db1.table.sometext = db2.table.sometext and db1.table.somenum = db2.table.sumnum;

Add your own comment.