CODEWITHSUNDEEP
mysql
Paxton Casimir
Paxton Casimir

Reputation: 3

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

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.

Upvotes: 0

Views: 1437

Answers (2)

piotrgajow
piotrgajow

Reputation: 2950

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;

Upvotes: 0

xQbert
xQbert

Reputation: 35323

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:

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

Upvotes: 0

Related Questions