MYSQL query to set date column to null except for current date

Question

I am working on MYSQL database and I am running into an issue. I have a column with dates on it. However, I am trying to set the whole column to null only if the entire date column is already filled with data.

What I have tried:

update users set date = null where date is not null

But, when I do this it's resetting the current date. How do I set the date to null only if the entire data column is filled with data?

For example I have a Random Name selector Program that selects a random user.

My table looks like this:

ID Name Date 1 A
2 B
3 C

When I select a Random User today:

ID Name Date 1 A
2 B
3 C 2016-03-12

My code is working fine for this scenario. I want keep the date for C and then select someone randomly tomorrow. If I run my code tomorrow it will only select between A and B and will put a date next to their name. For Example lets say it selected B then my table will look like:

ID Name Date 1 A
2 B 2016-03-13 3 C 2016-03-12

And on Monday my program has no choice but to select person A and my table will look like:

ID Name Date 1 A 2016-03-14 2 B 2016-03-13 3 C 2016-03-12

So far my program is working fine. What I want is on Tuesday(03/15/2016) when I go to run my random selector I want it to reset the dates for B and C to be set to Null and leave A with 2016-03-14 so that A doesn't get selected on Tuesday.

You may have default value for column.Check your table structure. — Abhishek Ginani, Mar 12 '16 at 17:22
Refer to [this link](http://stackoverflow.com/questions/11312433/how-to-alter-a-column-and-change-the-default-value) to set the **DEFAULT VALUE** and after that run the above **UPDATE QUERY** — Asif Mehmood, Mar 12 '16 at 17:30
To clarify: if all columns of the record are null, you want to set the date to null? a example with expected results would be useful. or are you saying you have multiple dates stored in one field and if all of them are not filled in you want to set it to null.. — xQbert, Mar 12 '16 at 17:45
# ID, Name, Date '93', 'a', NULL '94', 'b', NULL '95', 'c', NULL Here is a table I have with 3 columns. What I have so far is I am able to add and remove people to this table in MYSQL. I am also able to select a random user out of the people on the table and it will update the date column with current date. What I am trying to do is to wait till the entire Date column is filled with different dates in this case of a,b,c. I want the date column to have todays, tomorrows, and day after tomorrows date before it will reset the column to null and then select random user from these three later. — Paxton Casimir, Mar 12 '16 at 17:47
Please [edit] your question and add this information instead of posting it in a comment. And while editing you could also supply your desired result in tabular form because then we know at a glance what you need. — PerlDuck, Mar 12 '16 at 18:02
@xQbert here is what I start with: # ID, Name, Date '93', 'a', NULL '94', 'b', NULL '95', 'c', NULL — Paxton Casimir, Mar 12 '16 at 18:27
Again, @PaxtonCasimir, please [edit] your question and append that with appropriate formatting. Table structure is quite hard to read when given inline without any linebreaks and such. — PerlDuck, Mar 12 '16 at 18:33
Um... why not a two step process... `select count(*) cnt from users where date is null` If cnt=0 then execute your `update users set date = null` — xQbert, Mar 12 '16 at 18:36

xQbert · Accepted Answer · 2016-03-12T19:35:34.200

0

I'd prefer a two step process but this may work if you have to have one...

What this does is evaluate total number of records against those records where date is not null when they match it updates every record to null date. When they don't match it sets the date field equal to the existing date field.

Maybe this... (UNTESTED)

Update users u set u.`date` = NULL
where u.ID exists 
(Select 1 
 from users iu
 where (Select count(*) from users) = (Select count(*) from users where `date` is not null)
 and u.id = iu.id)

This touches every record every time... I'd prefer a two step process as it only touches the records when it needs to.

Two Steps:

select count(*) cnt from users where date is null;
if cnt = 0 then update users set date = null;

edited Mar 12 '16 at 19:35

answered Mar 12 '16 at 18:41

xQbert

34,733
2
41
62

@xQbert...I tried that and I am getting the following error: Error Code: 1093. You can't specify target table 'users' for update in FROM clause 0.000 sec – Paxton Casimir Mar 12 '16 at 19:28
We'll I did say perhaps.... I've reformatted it to avoid the from clause but I'm not sure if the engine will allow it. (untested still) it's why I really like the two step approach. – xQbert Mar 12 '16 at 19:36
xQbert....thank you so much for your help. I used the following: PreparedStatement count = con .prepareStatement("SELECT @cnt := count(*) FROM users WHERE Date is null"); ResultSet cnt = count.executeQuery(); PreparedStatement update = con .prepareStatement("UPDATE users SET date = null where (@cnt = 0 and Date != curdate())"); update.executeUpdate(); – Paxton Casimir Mar 15 '16 at 13:35

score 0 · Answer 2 · answered Mar 12 '16 at 23:43

This query should set date to null on all rows, except ones with current date, and only if all date rows are set:

    UPDATE users u
    JOIN    (
        SELECT count(id) nulls
        FROM users
        WHERE date IS NULL
        ) cnt
    SET u.date = NULL
    WHERE u.date != DATE(NOW())
    AND cnt.nulls = 0;

MYSQL query to set date column to null except for current date

2 Answers2