Updating value with SUM, for each record

Transact-SQL
1

This one is a bit hard to explain, but I'm stuck. I am performing a SUM calculation and I want to update each record in the table with it's own SUM, however the query below is summing ALL records where jobnum='21231' and updating the value with that number, instead of updating each record with it's own value.

If I change jobnum to the unique id value, such as id='187774' it sums correctly and updates that one row correctly. I just need it to run on a larger scale. Once I know it's working I need to remove the where clause altogether and update approx. 200,000 records.

UPDATE hours, (SELECT ROUND(SUM(TIME_TO_SEC(TIMEDIFF(hours.clockout, hours.clockin))/3600) * classes.classrate) as timecalc FROM hours, classes WHERE hours.clockclass = classes.classname and hours.jobnum = '21231') as s SET hours.recordcost = s.timecalc WHERE hours.jobnum = '21231' AND clockout IS NOT NULL

I tried to group by hours.id and it does not change a thing. What else can be done? I hope this is clear enough to understand, I will try to explain further if needed.
Thank you!

2

Perhaps this will help. (hours table trimmed up to only include a few records)

CREATE TABLE IF NOT EXISTS `classes` (
  `cid` int(11) NOT NULL AUTO_INCREMENT,
  `classname` varchar(20) NOT NULL,
  `classrate` int(11) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=28 ;

--
-- Dumping data for table `classes`
--

INSERT INTO `classes` (`cid`, `classname`, `classrate`) VALUES
(1, 'management', 0),
(2, 'project managers', 0),
(3, 'administration', 0),
(4, 'shop supervisors', 0),
(5, 'ficep', 150),
(6, 'saw', 98),
(7, 'fabrigear', 175),
(8, 'waterjet', 150),
(9, 'fiber6', 150),
(10, 'accukut', 150),
(11, 'python', 140),
(12, 'welding', 98),
(13, 'fitting', 98),
(14, 'robotics', 98),
(15, 'production', 55),
(16, 'non-production', 55),
(17, 'detailing', 95),
(18, 'engineering', 250),
(19, 'programming', 80),
(20, 'shipping', 0),
(21, 'installation', 95),
(22, 'quality control', 95),
(23, 'checking', 0),
(24, 'brake press', 98),
(25, 'helper', 55),
(26, 'machining', 115),
(27, 'apprentice', 80);



CREATE TABLE IF NOT EXISTS `hours` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empid` int(11) NOT NULL,
  `jobnum` int(11) NOT NULL,
  `series` int(11) NOT NULL,
  `clockclass` varchar(20) DEFAULT NULL,
  `clockin` datetime NOT NULL,
  `clockout` datetime DEFAULT NULL,
  `hours` int(11) DEFAULT NULL,
  `minutes` int(11) DEFAULT NULL,
  `seconds` int(11) DEFAULT NULL,
  `report` varchar(200) DEFAULT NULL,
  `recordcost` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=189025 ;

--
-- Dumping data for table `hours`
--

INSERT INTO `hours` (`id`, `empid`, `jobnum`, `series`, `clockclass`, `clockin`, `clockout`, `hours`, `minutes`, `seconds`, `report`, `recordcost`) VALUES
(211, 91, 0, 0, 'detailing', '2014-06-02 09:10:42', '2014-06-02 15:23:49', 6, 13, 7, NULL, NULL),
(210, 91, 0, 0, 'detailing', '2014-06-02 07:10:25', '2014-06-02 09:10:34', 2, 0, 9, NULL, NULL),
(209, 44, 0, 0, 'detailing', '2014-06-02 08:31:44', '2014-06-02 09:43:02', 1, 11, 18, NULL, NULL),
(208, 44, 12650, 100, 'detailing', '2014-06-02 08:01:21', '2014-06-02 08:31:43', 0, 30, 22, NULL, NULL),
(207, 8, 12675, 100, 'detailing', '2014-06-02 07:38:19', '2014-06-02 13:07:19', 5, 29, 0, NULL, NULL),
(206, 68, 12621, 300, 'detailing', '2014-06-02 07:17:17', '2014-06-02 11:41:51', 4, 24, 34, NULL, NULL),
(205, 57, 11223, 700, 'detailing', '2014-06-02 06:50:04', '2014-06-02 15:36:37', 8, 46, 33, NULL, NULL),
(204, 55, 11585, 1900, 'detailing', '2014-06-02 06:29:48', '2014-06-02 09:58:13', 3, 28, 25, NULL, NULL),
(203, 19, 12220, 600, 'detailing', '2014-06-02 05:51:40', '2014-06-02 16:31:15', 10, 39, 35, NULL, NULL),
(202, 55, 12620, 200, 'detailing', '2014-06-02 05:23:18', '2014-06-02 06:29:40', 1, 6, 22, NULL, NULL),
(201, 30, 12148, 1400, 'detailing', '2014-06-02 04:32:46', '2014-06-02 14:00:32', 9, 27, 46, NULL, NULL);
3

On MSSQL you can do:

update h
   set h.recordcost=round(datediff(second,h.clockin,h.clockout)/3600.*c.classrate,2)
  from hours as h
       inner join classes as c
               on c.classname=h.clockclass
 where h.jobnum='21231'
   and h.couckout is not null
;

But you are using MySQL, so it'll probably be:

update hours as h
       inner join classes as c
               on c.classname=h.clockclass
   set h.recordcost=round(timestampdiff(second,h.clockin,h.clockout)/3600.*c.classrate,2)
 where h.jobnum='21231'
   and h.couckout is not null
;
1 Like
4

Thank you very much this appears to have worked great!