How to replace a specific datetime value with NULL in MySQL column?

Question

Not being an SQL expert, I am having trouble with a simple replacement of a specific date-time value with NULL in MySQL. The nullable last_login DATETIME column in question may have values of '1900-01-01 00:00:00.000' as a result of populating rows from an Excel spreadsheet which do not have that date (i.e. empty cells). Each database row is uniquely identified by an id INT column. I tried:

UPDATE user_profiles
SET last_login = NULL
WHERE id IN
(SELECT id FROM user_profiles WHERE last_login = '1900-01-01 00:00:00.000');

But MySQL (and I suspect other databases as well) protests that the WHERE clause applies to the same column as the one being updated. What is the solution?

why not simply update user_profiles set last_login = NULL where last_login = '1900-01-01 00:00:00.000'? — proskor
– proskor, Commented Jan 28, 2016 at 22:30

Marcinek · Accepted Answer · 2016-01-28 22:30:47Z

2

You don't have to use a sub query in your where statement:

UPDATE user_profiles
SET last_login = NULL
WHERE last_login = '1900-01-01 00:00:00.000';

answered Jan 28, 2016 at 22:30

Marcinek

2,1171 gold badge21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1408140 Over a year ago

This works fine. I was clearly over-complicating the query.

Jerome · Accepted Answer · 2025-02-27 01:53:51Z

1

UPDATE CUSTOMER_ORDER
SET  PRINTED_DATE = NULL
where STATUS = 'F' and PRINTED_DATE = ''

This is in an ERP database. When the date = '', that is when reports will display 1900-01-01 etc.

edited Feb 27 at 1:53

Jerome

9041 gold badge12 silver badges40 bronze badges

answered Feb 25 at 22:38

Tom H

112 bronze badges

Comments

Jimmy C. Broadhead Jr. · Accepted Answer · 2016-01-28 22:41:52Z

0

If you want to use a sub-query with the same fields, you'll need to identify the tables. Either by using the table names or by giving them an alias. So using your code, this should work:

UPDATE user_profiles u
SET u.last_login = NULL
WHERE u.id IN
(SELECT x.id FROM user_profiles x WHERE x.last_login = '1900-01-01 00:00:00.000');

That being said, like most people are saying, you don't need to use a subquery:

UPDATE user_profiles
SET last_login = NULL
WHERE last_login = '1900-01-01 00:00:00.000';

answered Jan 28, 2016 at 22:41

Jimmy C. Broadhead Jr.

12 bronze badges

Collectives™ on Stack Overflow

How to replace a specific datetime value with NULL in MySQL column?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related