Mysql日期更新无法正常工作

Question

I have one issue while updating mysql table. I have two fields real_date (Type : varchar(20)), event_date(Type: date()). Below are few values

real_date   event_date  event_date(Need to b updated.)
1985-03-20  0000-00-00    1971-03-20
1989-08-20  0000-00-00    1971-08-20 
1993-04-30  0000-00-00    1971-04-30

I want to update event_date to as shown above. Fetch month and date from real_date and keep year as 1971. I am using below queries but it doesn't work Can you please help me.

Query1 : Update table set event_date= STR_TO_DATE(concat('1971','-',DATE_FORMAT(real_date, "%m-%d")),'%Y-%m-%d') where real_date IS NOT NULL AND real_date != '0000-00-00' AND real_date <> '' AND event_date='0000-00-00'

Query2 : Update table set event_date= STR_TO_DATE(concat('1971','-',SUBSTR(real_date, 6, 5)),'%Y-%m-%d') where real_date IS NOT NULL AND real_date != '0000-00-00' AND real_date <> '' AND event_date='0000-00-00'

Query3 : Update table set event_date=concat('1971-',DATE_FORMAT(real_date, "%m-%d")) where real_date IS NOT NULL AND real_date != '0000-00-00' AND real_date <> '' AND event_date='0000-00-00'

But none of above works. Please help me.

Answer 1

use OR

UPDATE TableName 
SET    event_date = CONCAT('1971-', DATE_FORMAT(real_date, '%m-%d')) 
WHERE  real_date IS NOT NULL OR 
       real_date != '0000-00-00' OR 
       real_date <> '' OR 
       event_date = '0000-00-00'

• SQLFiddle Demo