Update of holidays in SQL Server table

Question

I have an SQL Server table called Dm_tempo. Its fields are:

DtRef
AnoRef
MesRef
DiaRef
MesExtenso
CodDiaSemana
SemanaNumero
IndFeriado

And already have in it days marked as holidays (in this case, Indferiado = 'SIM'). But what I wanted to do was the following, for example: if a date that was on a Friday (Coddiasemana = 6) is not a holiday, but the day before it was, I have to do an UPDATE for this Friday to be considered a holiday too (like a holiday). I tried something like this:

UPDATE DM_Tempo
SET IndFeriado = 'SIM'
WHERE AnoRef IN (2017,2018)
AND IndFeriado = 'NÃO'
AND CodDiaSemana = 6
AND IndFeriado IN (
SELECT IndFeriado FROM DM_TempoEmpresa T2 WHERE T2.DtRef = 
DATEADD(DAY,-1,T1.DtRef) AND T2.IndFeriado = 'SIM'
)
order by 2,1

But it didn’t work. What could I do?

Answer 1

That should solve your problem:

UPDATE tabela_tempo SET IndFeriado = 'SIM'
FROM DM_Tempo as tabela_tempo
WHERE tabela_tempo.AnoRef IN (2017, 2018)
  AND tabela_tempo.IndFeriado = 'NÃO'
  AND tabela_tempo.CodDiaSemana = 6
  AND EXISTS (SELECT 1 
                FROM DM_TempoEmpresa T2 
                WHERE T2.DtRef = DATEADD(DAY, -1, tabela_tempo.DtRef)
                  AND T2.IndFeriado = 'SIM')
order by 2,1

You weren’t "mapped" the table outside (which will be updated) - you actually referred to it as T1 but without adding alias to update)