Copy fields with calculation in one statement

Hi All,

Tinkering with MySQL and I’m attempting to copy 3 fields from table A to table B. Simple enough, but while performing this copy, I’d like a 4th field to be calculated using the time_to_sec function.

As a standalone statement the below works fine for doing a time_to_sec select:


select round(time_to_sec(timediff(clockon,clockoff))/60) as minutes_worked from tableA where employee = 'John';

At the moment I have the below, which is an amalgamation of the above plus a straightforward field copy, but it doesn’t work. If anyone can point me in the right direction (or even what to search for on the web since I haven’t been able to stumble onto anything relevant) that’d be most welcome.


insert into tableB (employee, clockon, clockoff, minutes_worked) select employee, clockon, clockoff, minutes_worked as round (time_to_sec(timediff(clockon,clockoff))/60) from tableA where employee = 'John';

Thanks

MP

you are more likely to get a response if you present your query with a modicum of formatting, e.g. indents and line breaks, thus relieving us of the onerous task of first doing it ourselves before attempting to read and understand your query

INSERT 
  INTO tableB 
     ( employee
     , clockon
     , clockoff
     , minutes_worked) 
SELECT employee
     , clockon
     , clockoff
     , [COLOR="Blue"]ROUND(TIME_TO_SEC(TIMEDIFF(clockon,clockoff))/60)[/COLOR] 
  FROM tableA 
 WHERE employee = 'John'

Apologies for not conducting research prior to posting. Thanks for the heads up.

Obviously I need more sleep. I only just noticed you posted a solution along with the correction! Thanks so much for that r937!

:slight_smile: