conditional update records mysql query

Question

Is there any single msql query which can update customer DOB? I want to update the DOB of those customers which have DOB greater than current date. example:- if a customer have dob 2034 update it to 1934 , if have 2068 updated with 1968.

There was a bug in my system if you enter date less than 1970 it was storing it as 2070. The bug is solved now but what about the customers which have wrong DOB. So I have to update their DOB.

All customers are stored in customer_entity table and the entity_id is the customer_id

Details is as follows:-

desc customer_entity
    -> ;
+------------------+----------------------+------+-----+---------------------+----------------+
| Field            | Type                 | Null | Key | Default             | Extra          |
+------------------+----------------------+------+-----+---------------------+----------------+
| entity_id        | int(10) unsigned     | NO   | PRI | NULL                | auto_increment | 
| entity_type_id   | smallint(8) unsigned | NO   | MUL | 0                   |                | 
| attribute_set_id | smallint(5) unsigned | NO   |     | 0                   |                | 
| website_id       | smallint(5) unsigned | YES  | MUL | NULL                |                | 
| email            | varchar(255)         | NO   | MUL |                     |                | 
| group_id         | smallint(3) unsigned | NO   |     | 0                   |                | 
| increment_id     | varchar(50)          | NO   |     |                     |                | 
| store_id         | smallint(5) unsigned | YES  | MUL | 0                   |                | 
| created_at       | datetime             | NO   |     | 0000-00-00 00:00:00 |                | 
| updated_at       | datetime             | NO   |     | 0000-00-00 00:00:00 |                | 
| is_active        | tinyint(1) unsigned  | NO   |     | 1                   |                | 
+------------------+----------------------+------+-----+---------------------+----------------+
11 rows in set (0.00 sec)

And the DOB is stored in the customer_entity_datetime table the column value contain the DOB. but in this table values of all other attribute are also stored such as fname,lname etc. So the attribute_id with value 11 is DOB attribute.

mysql> desc customer_entity_datetime;
+----------------+----------------------+------+-----+---------------------+----------------+
| Field          | Type                 | Null | Key | Default             | Extra          |
+----------------+----------------------+------+-----+---------------------+----------------+
| value_id       | int(11)              | NO   | PRI | NULL                | auto_increment | 
| entity_type_id | smallint(8) unsigned | NO   | MUL | 0                   |                | 
| attribute_id   | smallint(5) unsigned | NO   | MUL | 0                   |                | 
| entity_id      | int(10) unsigned     | NO   | MUL | 0                   |                | 
| value          | datetime             | NO   |     | 0000-00-00 00:00:00 |                | 
+----------------+----------------------+------+-----+---------------------+----------------+
5 rows in set (0.01 sec)

Thanks.

Answer 1

Something like:

SELECT value_id, value, DATE_SUB(value, INTERVAL 100 YEAR) as 'correct'
FROM customer_entity_datetime
WHERE value > NOW()

should give you those rows. To update them, just apply:

UPDATE customer_entity_datetime
SET value = DATE_SUB(value, INTERVAL 100 YEAR)
WHERE value > NOW()