Services
Technology Web App Development Mobile App Development Windows App Development Database Development UI/UX Development Cloud Development SEO Digital Marketing Guest Posting Content Writing Artificial Intelligence Data Science ERP Development CRM Development CMS Development LMS Development Business Listing Blockchain Technology Startup Promotion AI Tools Integration
Products
Data Converter Cleaner TUC - Unit Converter Import Export Survey Manager Tweet Controller
Developer
Article Blog Forum Interview Beginner News Startup Quiz
Users
Career
Apply Job Internship
Pricing
Login Sign Up Get a Quote

forum

home / developersection / forums / writing a query to update records based on a condition in sql server
Ask Question
Anubhav Sharma
Anubhav Sharma

Student

The Anubhav portal was launched in March 2015 at the behest of the Hon'ble Prime Minister for retiring government officials to leave a record of their experiences while in Govt service .

Message

1 Answers

Ravi Vishwakarma
Ravi Vishwakarma 16 Jul 2024
Report Abuse

To update the Status column in your Orders table to 'Completed' for all orders where the OrderDate is older than 30 days, you can use the UPDATE statement with a WHERE clause. 

Here is the query:

--using statement
UPDATE Orders
SET [Status] = 'Completed'
WHERE OrderDate < DATEADD(DAY, -30, GETDATE());

-- Using one time variable 
Declare @LastDate DateTime = DATEADD(DAY, -30, GETDATE());
UPDATE Orders
SET [Status] = 'Completed'
WHERE OrderDate < @LastDate;

Explanation:

  • UPDATE Orders: Specifies the table to update.
  • SET Status = 'Completed': Sets the Status column to 'Completed'.
  • WHERE OrderDate < DATEADD(DAY, -30, GETDATE()): Filters the rows to update where the OrderDate is more than 30 days older than the current date.
    • GETDATE(): Returns the current date and time.
    • DATEADD(DAY, -30, GETDATE()): Subtracts 30 days from the current date, giving you the date 30 days ago.

This query will update  Status all orders placed more than 30 days ago to 'Completed'.

Read more

How to Write a Query to Find the Second Highest Salary in SQL Server?

SQL Query to Pivot Data from Rows to Columns in SQL Server

Help with Writing a Query to Find Orphaned Records in SQL Server

How to Write a Query to Get Top N Records per Group in SQL Server?

SQL Query to Find Duplicate Records in a Table in SQL Server

advertising