UPDATE based on this SELECT - How to?

Question

I've been working in a time attendance application and need some enlightment.
This is my current SQL query:

 SELECT M.IdMarcacao, M.IdFuncionario, M.Data, M.Hora, Extra, 
    (M.Hora-(convert(varchar(11),dateadd(ms,cast(Extra*3600000 as bigint),'12/30/1899'),108))) as teste 
 FROM TimeReport.dbo.Marcacoes M 
      INNER JOIN TimeReport.dbo.Resultados R ON M.IdFuncionario = R.IdFuncionario
                                                AND M.Data = R.Data 
 WHERE (R.Extra <> 0 AND M.[Tipo Marcacao] = 'SAI') 
       AND M.Hora=(SELECT max(hora) 
                   FROM timereport.dbo.marcacoes 
                   WHERE data = M.Data)

This returns the lines where:
1 - The person has made overtime hours.
2 - The last logout time
3 - The total of overtime hours.
4 - The difference between logout time and overtime = schedule time
5 - Make sure it's a logout time (Tipo = SAI)
Without entering into much detail about the application itself, what I really need is to turn this into an UPDATE statement.
I used to do this:

UPDATE [TimeReport].[dbo].[Marcacoes]
SET [Hora] = [Hora] - convert(datetime,'01:00:00',108)
WHERE [Hora] > '1899-12-30 19:00:00.000'

For every single hour until 0pm :( It's not a good solution I know.
This update, will change the original logout time to minus 1 hour if the worker left at 19h, when the company schedule is 18h.
What I am trying to do, is simplify and automate the process.
Which brings my previous question... How can I do a update based on that select statement?
Or... in other words, something like:

UPDATE TimeReport.dbo.Marcacoes
SET Hora = (The value from the statement above, field "teste")
WHERE IdMarcacao = IdMarcacao(from the statement above)

Note: This "IdMarcacao" is a unique identifier for the row.
Thank you!

I believe this was addressed here: http://stackoverflow.com/q/2334712/535275 — Scott Hunter, Jan 11 '12 at 01:31
Can't be that simple :| this make my day, lol. Let me try out. — Filipe YaBa Polido, Jan 11 '12 at 01:33
Nevermind, I tried a simple workaround. I created a VIEW and I'm now trying to use those values in the UPDATE statement :| I must RTFM about MSSQL Views. Thanks — Filipe YaBa Polido, Jan 11 '12 at 01:47

score 2 · Accepted Answer · answered Jan 11 '12 at 01:52

2

update TimeReport.dbo.Marcacoes set
  Hora = (M.Hora-(convert(varchar(11),dateadd(ms,cast(Extra*3600000 as bigint),'12/30/1899'),108)))
--select M.IdMarcacao, M.IdFuncionario, M.Data, M.Hora, Extra, (M.Hora-(convert(varchar(11),dateadd(ms,cast(Extra*3600000 as bigint),'12/30/1899'),108))) teste
from TimeReport.dbo.Marcacoes M 
INNER JOIN TimeReport.dbo.Resultados R ON M.IdFuncionario = R.IdFuncionario AND M.Data = R.Data 
WHERE (R.Extra <> 0 AND M.[Tipo Marcacao] = 'SAI') 
AND M.Hora=(select max(hora) from timereport.dbo.marcacoes where data = M.Data)

answered Jan 11 '12 at 01:52

Dale K

25,246
15
42
71

Now I have another problem... I can't run this more than one time or it will mess up the logout times. I must set to zero the "Extra" on "Resultados". This will be my homework, lol. Thanks. – Filipe YaBa Polido Jan 11 '12 at 02:06
Off the top of my head you'll need to use a temp table for that (using select into), which will have M.id, R.Id, M.FieldToUpdateNewValue, R.FieldToUpdateNewValue, followed by 2 insert statements which join the temp table onto the table to update. That should get you started... – Dale K Jan 11 '12 at 02:28
That's a big load for my truck (Portuguese expression). I go with update table set Extra=0 where extra <> 0 :) eheh Thanks. – Filipe YaBa Polido Jan 11 '12 at 02:30
The only danger with that is if a new record is inserted into the Marcacoes table between the time its updated and the time you update Resultados you'll have an invalid record. – Dale K Jan 11 '12 at 03:58

UPDATE based on this SELECT - How to?

1 Answers1