CODEWITHSUNDEEP
mysqldefault-value
user3176519
user3176519

Reputation: 393

MySql update all fields in a column to their default value

I would like to be able to set default values for home_country field using update statement.

This is by database table:

CREATE TABLE `countries` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(70) COLLATE utf8_unicode_ci NOT NULL,
  `home_country` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
) ENGINE=MyISAM;

I'm using :

$sql = "UPDATE countries SET home_country = DEFAULT WHERE id = 1"

But for some reason it's not working for me. In the above Country, with ID 1 should have home_country value set to 1, while all other reset to 0

Upvotes: 1

Views: 3327

Answers (2)

Glorious Kale
Glorious Kale

Reputation: 1313

You may use the function DEFAULT() instead.

$sql = "UPDATE countries SET home_country = DEFAULT(home_country) WHERE id = 1"

https://www.geeksforgeeks.org/mysql-default-function/

Upvotes: 1

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

You are better off just running the value directly

UPDATE countries SET home_country = 0 WHERE id = 1;

If you want the default value dynamically set, you'll have to do something insane like this

UPDATE countries SET home_country =
(SELECT column_default FROM information_schema.columns
WHERE table_schema=DATABASE()
AND table_name ='countries'
AND column_name = 'home_country') WHERE id = 1;

Upvotes: 3

Related Questions