CODEWITHSUNDEEP
mysql
user95437
user95437

Reputation: 69

Update multiple values, in different columns, to null based on what the value is intitially in Mysql

There are different date columns in a table and some rows in the table have 1st of January values, "YYYY-01-01".

I can do a query and find all those rows with any date field that has 1st Jan YYYY. 

select * from table 
where dob like '%01-01' 
OR start_date like '%01-01'
OR ...

But how do I then set those values to NULL?

I've tried:

update table 
set dob = NULL
where dob='%01-01';
update table 
set start_date= NULL
where start_date='%01-01';
...

That doesn't work...

Upvotes: 0

Views: 39

Answers (2)

Roy Holzem
Roy Holzem

Reputation: 870

I would use this:

UPDATE table SET dob = null WHERE dob LIKE '%01-01'

EDIT: corrected

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269643

Use update:

update table
    set start_date = NULL
    where day(start_date) = 1 and month(start_date) = 1;

update table
    set dob = NULL
    where day(dob) = 1 and day(dob) = 1;

You might as well do a separate update for each column. Although the logic can be encapsulated into a single query, that is just a more complicated query.

Don't use like on dates. like is for strings. MySQL has plenty of appropriate date functions.

Do note: Some people really are born on January 1st.

Upvotes: 1

Related Questions