How to change an incorrect datatype for a birth date field from VARCHAR to DATE

I made a mistake when creating my users table with a birthdate field and instead of putting the DATE datatype, I put a VARCHAR!

So now my users table looks like this:

CREATE TABLE IF NOT EXISTS users 
(
  id INT    UNSIGNED NOT NULL,
  birthdate VARCHAR (200) NOT NULL,

  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

and it is populated as follows (sample):

INSERT INTO users (id, birthdate) VALUES
  (1,'1991-01-23'),
  (2,'yyyy-01-23'),
  (3,'1991-mm-23'),
  (4,'1991-01-dd'),
  (5,''),
  (6,'1991-01-d3'),
  (7,'1983-05-23'),
  (8,'1991-0m-23'),
  (9,'19yy-01-23'),
  (10,'y991-01-23');

Now I want to update every incorrect birthdate to NULL, or set a default value like 2020-01-01. see my sqlfiddle here.

ALTER TABLE users ADD COLUMN DOB DATE;

UPDATE users
SET DOB = STR_TO_DATE(birthdate, '%Y-%m-%d')
WHERE birthdate REGEXP '[0-9]{4}-[0-9]{2}-[0-9]{2}';


https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=96c9978ccc667b9289cb3b7b223f28d7

StackExchange Database Administrators Q#288876, answer score: 4