CODEWITHSUNDEEP
phpmysqlstored-procedures
Nat
Nat

Reputation: 63

perform calculations on multiple users-own records in mysql

I need help extending a functional update query that performs calculations on one record to be able to perform calculations not only on one record in the database, but on all records associated with a particular user #.

Functionally, I need to extend an "edit this record" to "reevaluate all records of user#?"

Current calculations make use of 3 tables, sum a column, divide by the sum of another, and then creates a variable from that result(there are two columns summed separately to create two variables). Then I have a simple UPDATE query to update the record with the values of those variables. Each record has different values, and the sums will be different for every id# @lastid is the unique record id.(allinfsds.id1) I need to have the calculations done on all records that = a particular owners id (allinfsds.own_id)[i.e. WHERE allinfsds.own_id= usernum]

Any ideas??? Thanks ahead of time, Nat

    CREATE TABLE `allingred` (
  `id6` int(8) NOT NULL auto_increment,
  `usernum` varchar(255) default NULL,
  `fsdsnum` int(8) unsigned zerofill NOT NULL,
  `mfdfsds` varchar(255) default NULL,
  `maybe` decimal(2,1) NOT NULL default '1.0',
  `amount` float(10,2) default NULL,
  `unit` int(6) default NULL,
  `name` varchar(255) default NULL,
  `wgt` int(9) NOT NULL,
  PRIMARY KEY  (`id6`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=38 ;

    CREATE TABLE `weight` (
  `NDB_No2` int(8) unsigned zerofill NOT NULL,
  `Seq` smallint(6) NOT NULL,
  `amt2` decimal(5,3) NOT NULL,
  `Msre_Desc` varchar(80) NOT NULL,
  `Gm_Wgt` decimal(7,1) NOT NULL,
  `Num_Data_Pts` tinyint(4) default NULL,
  `Std_Dev` decimal(7,1) default NULL,
  `uni` int(7) NOT NULL auto_increment,
  PRIMARY KEY  (`uni`),
  KEY `fb_join_NDB_No2_INDEX` (`NDB_No2`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21731 ;

    CREATE TABLE `allinnot2` (
  `NDB_No` int(8) unsigned zerofill NOT NULL,
  `Water` decimal(10,2) default NULL,
  `Energ_Kcal` decimal(10,0) default NULL

    CREATE TABLE `allinfsds` (
  `id1` int(8) unsigned zerofill NOT NULL,
  `own_id` int(11) NOT NULL
  UNIQUE KEY `id` (`id1`),
  KEY `fb_groupbyorder_item_number_INDEX` (`item_number`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


    SET @cal = (SELECT SUM( Energ_Kcal * allingred.amount * Gm_Wgt) / SUM( allingred.amount * Gm_Wgt ) AS nut100
FROM  `allingred` 
LEFT JOIN weight ON allingred.unit = weight.uni
LEFT JOIN allinnot2 ON allingred.mfdfsds = allinnot2.NDB_No
LEFT JOIN allinfsds ON allingred.fsdsnum = allinfsds.own_id
WHERE fsdsnum = @lastid)

SET @prot = (SELECT SUM(Protein * allingred.amount * Gm_Wgt) / SUM( allingred.amount * Gm_Wgt ) AS nut100
FROM  `allingred` 
LEFT JOIN weight ON allingred.unit = weight.uni
LEFT JOIN allinnot2 ON allingred.mfdfsds = allinnot2.NDB_No
WHERE fsdsnum = @lastid)

UPDATE `allinnot2` SET
  `Energ_Kcal` = @cal,
  `Protein` = @prot
WHERE `NDB_No` = @lastid

Upvotes: 0

Views: 162

Answers (1)

Basti
Basti

Reputation: 4042

How to update multiple tuples at once

If you have a list of Ids you want to update, use

UPDATE `myTable` SET `myColumn` = 'newValue' 
WHERE `userId` IN (
    /*list of relevant Ids for instance: */ 15, 20, 63, 987
)

or if you dont have this list, but you can query the database for this list, use

UPDATE `myTable` SET `myColumn` = 'newValue' 
WHERE `userId` IN (
    SELECT `userId` FROM `myOtherTable` WHERE `relevantColumn` = 'value'
)

Beware that you are not allowed to use the same table as both the update target and source of ids in the subselect, so myTable != myOtherTable.

Upvotes: 1

Related Questions