CODEWITHSUNDEEP
phpsql
Maddy Dongra
Maddy Dongra

Reputation: 21

How to write this SQL query?

I am not a programmer, just a learner. I am testing something. I have a table namely test1 which have three columns id , date , status.

I want to change the status of the id which is 10 day old and have status as pass or fail or none to good.

Upvotes: 2

Views: 84

Answers (2)

Taryn
Taryn

Reputation: 247860

Since you did not specify the RDBMS I will provide an answer for MySQL and SQL Server. You did not provide many details about your request but you can do the following in MySQL:

UPDATE test1
SET status = 'good'
where date < DATE_ADD(now(), interval -10 day)
  AND status IN ('pass', 'fail', 'none');

Here is a sql fiddle with a working example.

In SQL Server your query would be:

UPDATE test1
SET status = 'good'
where date < DateAdd(day, -10, getdate())
  AND status IN ('pass', 'fail', 'none');

Here is a sql fiddle with that example.

Upvotes: 2

JeffO
JeffO

Reputation: 8053

Your requirements are vague.

This just looks at the pass records and sets them to fail when their date is over 10 days old.

update test1
set status = 'fail'
where status = 'pass'
and date < dateadd(d,-10, getdate())

Upvotes: 0

Related Questions