CODEWITHSUNDEEP
mysqlsqljoin
user2180613
user2180613

Reputation: 739

Updating default values on a mysql table

In order to update a table and assign a default value on a column, one may use a statement such as:

UPDATE table SET a = 'value', b = DEFAULT WHERE ISNULL(c).

However, the following will not work.

UPDATE table SET a = IF(FALSE, 'value', DEFAULT)

Now what do I do when I want to update a column and assign a default value on it when passed to a function statement?

Upvotes: 1

Views: 125

Answers (1)

Marcus Adams
Marcus Adams

Reputation: 53870

According to MySQL documentation, you can use DEFAULT in expressions in this form:

DEFAULT(col_name)

So, your query should be changed to:

UPDATE table SET a = IF(FALSE, 'value', DEFAULT(a))

This is currently spelled out on the documentation for the INSERT statement, but unfortunately, not for the UPDATE statement which is why you may have been stumped:

You can also use DEFAULT(col_name) as a more general form that can be used in expressions to produce a given column's default value.

Upvotes: 2

Related Questions