revert rows to default column value mysql

Question

I am using MySQL community server 5.1. Is there a way to just set all the rows back to their default column values with one query?

I just added a lot of rows. I had default values for most of the columns specified, but for some reason all the columns values were set = 0 instead. I was wondering if there is a query to set all the column values to their default value, and I would need the ability for a where clause too.

something like this for example:

update table set values = values(default) where id > 300;

Thanks!

Answer 1

There is a DEFAULT keyword available in INSERT and UPDATE queries which you can use in place of any real column value:

update my_table set my_column = DEFAULT where id > 300;

As documented in the MySQL Manual

Note that there is also a DEFAULT() function, as mentioned in a previous answer; with which you can select any column's default value for use.