CODEWITHSUNDEEP
sql
Tortooga
Tortooga

Reputation: 215

SQL query to copy column contingent on contents

Currently I have an SQL query that looks like this:

INSERT INTO my_table
(Date, Name, Age, City)
SELECT
DATE_ADD (Date, INTERVAL 7 DAY), Name, Age, City
FROM my_table

This allows me to carry forward to new weeks with an incrimenting "Date" column, while copying over consistent columns (I have other numerical columns that don't copy over). However I also have a column called "Status" which I want to copy forward only if the status is "Hold" or "Pending". If the status is either of those, I want it to carry forward as "Needs Examining".

I know I need some type of if function within my SQL code, but I am uncertain how exactly to accomplish this.

Upvotes: 1

Views: 83

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

You can do it by the CASE, if the Status is not matching you can add as empty string.

INSERT INTO my_table (Date, Name, Age, City, Status)
SELECT DATE_ADD (Date, INTERVAL 7 DAY), Name, Age, City, 
       CASE WHEN Status IN ('Hold', 'Pending') THEN 'Needs Examining'
       ELSE '' END AS Status
FROM my_table

Or, if you want to INSERT only for the Hold, Pending status you need to add the condition in the WHERE clause and in the SELECT you can place your required content:

INSERT INTO my_table (Date, Name, Age, City, Status)
SELECT DATE_ADD (Date, INTERVAL 7 DAY), Name, Age, City, 'Needs Examining' AS Status
FROM my_table
WHERE Status IN ('Hold', 'Pending')

Upvotes: 1

Related Questions