MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.7.39


CREATE TABLE IF NOT EXISTS users (
  id int(6) unsigned NOT NULL,
  birthdate varchar(200) NOT NULL,
  PRIMARY KEY (id)
)DEFAULT CHARSET=utf8;
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');

Records: 10  Duplicates: 0  Warnings: 0


ALTER TABLE users ADD new_bdate DATE;  -- add column to hold valid values

Records: 0  Duplicates: 0  Warnings: 0


ALTER TABLE users MODIFY birthdate VARCHAR (200) NULL;  
-- make birthdate nullable
-- this is important for the STR_TO_DATE function.
                                                          

Records: 0  Duplicates: 0  Warnings: 0


SELECT * FROM users;


id
birthdate
new_bdate


1
1991-01-23
null

2
yyyy-01-23
null

3
1991-mm-23
null

4
1991-01-dd
null

5

null

6
1991-01-d3
null

7
1983-05-23
null

8
1991-0m-23
null

9
19yy-01-23
null

10
y991-01-23
null


START TRANSACTION;
UPDATE users SET  birthdate = NULL
WHERE birthdate REGEXP '[a-zA-Z]' OR birthdate = '';
UPDATE users SET new_bdate = birthdate WHERE birthdate IS NOT NULL;
UPDATE users SET birthdate = new_bdate WHERE new_bdate IS NOT NULL;
COMMIT;

Rows matched: 8  Changed: 8  Warnings: 0



Rows matched: 2  Changed: 2  Warnings: 0



Rows matched: 2  Changed: 0  Warnings: 0


SELECT * FROM users;


id
birthdate
new_bdate


1
1991-01-23
1991-01-23

2
null
null

3
null
null

4
null
null

5
null
null

6
null
null

7
1983-05-23
1983-05-23

8
null
null

9
null
null

10
null
null


ALTER TABLE users DROP COLUMN new_bdate;

Records: 0  Duplicates: 0  Warnings: 0


SELECT * FROM users;


id
birthdate


1
1991-01-23

2
null

3
null

4
null

5
null

6
null

7
1983-05-23

8
null

9
null

10
null

id	birthdate	new_bdate
1	1991-01-23	1991-01-23
2	null	null
3	null	null
4	null	null
5	null	null
6	null	null
7	1983-05-23	1983-05-23
8	null	null
9	null	null
10	null	null

id	birthdate
1	1991-01-23
2	null
3	null
4	null
5	null
6	null
7	1983-05-23
8	null
9	null
10	null

id	birthdate	new_bdate
1	1991-01-23	null
2	yyyy-01-23	null
3	1991-mm-23	null
4	1991-01-dd	null
5		null
6	1991-01-d3	null
7	1983-05-23	null
8	1991-0m-23	null
9	19yy-01-23	null
10	y991-01-23	null