CODEWITHSUNDEEP
mysqlsql
user3119773
user3119773

Reputation: 83

Update certain columns based on date. Best way to approach this issue

I have a table I need to update. In this table there is a column for each month. If someone's start date is 2/13/2015 I need to update the February column with some data from a calculation.

So if Start_Date is 2/17/2015 then the value for the February column called FTEFeb needs to be populated. If the Start_Date is 3/09/2015 then the FTEMar column needs to be updated etc...

I was thinking a CASE statment would work but the SET column would be different based on the value in Start Date.

I've looked online for anything similar and nothing came up. Thanks in advance!

Upvotes: 0

Views: 31

Answers (1)

AdamMc331
AdamMc331

Reputation: 16690

You can use a case statement.

For your query, instead of preforming a CASE on the month, just write a case to update each column, and if the column is not the month you want you can set it to 0, or whatever your business rule is. Try this:

UPDATE myTable
SET 
  FTEJan = (CASE WHEN MONTH(startDate) = 1 THEN 1 ELSE 0 END),
  FTEFeb = (CASE WHEN MONTH(startDate) = 2 THEN 1 ELSE 0 END),
  FTEMar = (CASE WHEN MONTH(startDate) = 3 THEN 1 ELSE 0 END),
  FTEApr = (CASE WHEN MONTH(startDate) = 4 THEN 1 ELSE 0 END),
  FTEMay = (CASE WHEN MONTH(startDate) = 5 THEN 1 ELSE 0 END),
  FTEJun = (CASE WHEN MONTH(startDate) = 6 THEN 1 ELSE 0 END),
  FTEJul = (CASE WHEN MONTH(startDate) = 7 THEN 1 ELSE 0 END),
  FTEAug = (CASE WHEN MONTH(startDate) = 8 THEN 1 ELSE 0 END),
  FTESep = (CASE WHEN MONTH(startDate) = 9 THEN 1 ELSE 0 END),
  FTEOct = (CASE WHEN MONTH(startDate) = 10 THEN 1 ELSE 0 END),
  FTENov = (CASE WHEN MONTH(startDate) = 11 THEN 1 ELSE 0 END),
  FTEDec = (CASE WHEN MONTH(startDate) = 12 THEN 1 ELSE 0 END);

Here is an SQL Fiddle example.

Upvotes: 1

Related Questions