CREATE TABLE cl_moneybags (
  CRId bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор кошелька',
  ClientId bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Код клиента', -- FK из справочника клиентов
  MoneybagId tinyint(3) UNSIGNED NOT NULL COMMENT 'Код кошелька', -- FK из справочника кошельков
  Checked tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Используется?',
  TotalMoneybagCountRemains decimal(10, 2) DEFAULT 0.00 COMMENT 'Остаток кол-во месячных лимитов',
  LocalMoneybagCountRemains decimal(10, 2) DEFAULT 0.00 COMMENT 'Остаток кол-во дневных лимитов',
  TotalMoneybagSumRemains decimal(10, 2) DEFAULT 0.00 COMMENT 'Остаток месячного лимита - сумма',
  LocalMoneybagSumRemains decimal(10, 2) DEFAULT 0.00 COMMENT 'Остаток дневного лимита - сумма',
  TotalMoneybagCountSpend decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'Потрачено в месяц кол-ва',
  LocalMoneybagCountSpend decimal(10, 2) DEFAULT 0.00 COMMENT 'Потрачено в день кол-ва',
  TotalMoneybagSumSpend decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'Потрачено в месяц суммы',
  LocalMoneybagSumSpend decimal(10, 2) DEFAULT 0.00 COMMENT 'Потрачено в день суммы',
  PRIMARY KEY (CRId),
  INDEX ClientId (ClientId),
  UNIQUE INDEX UK_ClientId_MoneybagId (ClientId, MoneybagId),
  CONSTRAINT FK_cl_moneybags_ref_Moneybags FOREIGN KEY (MoneybagId)
  REFERENCES ref_moneybags (MoneybagId) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_client_moneybags_clients FOREIGN KEY (ClientId)
  REFERENCES cl_clients (ClientId) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = INNODB
CHARACTER SET cp1251
COLLATE cp1251_general_ci
COMMENT = 'Справочник кошельков клиентов';

CREATE TABLE ch_item_moneybags (
  CIRId bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор',
  ChekId bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Код чека',
  NumPos int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '№ позиции',
  MoneybagId tinyint(3) UNSIGNED DEFAULT NULL COMMENT 'Код кошелька', -- FK из справочника кошельков
  IPSumCalc decimal(10, 2) DEFAULT NULL COMMENT 'Сумма оплаты',
  IPCount decimal(14, 6) DEFAULT NULL COMMENT 'Количество оплаты',
  PRIMARY KEY (CIRId),
  INDEX FK_item_payments_rations (MoneybagId),
  UNIQUE INDEX UK_ch_item_moneybags (ChekId, NumPos, MoneybagId),
  CONSTRAINT FK_ch_item_moneybags FOREIGN KEY (ChekId, NumPos)
  REFERENCES ch_chek_items (ChekId, NumPos) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = INNODB
CHARACTER SET cp1251
COLLATE cp1251_general_ci
COMMENT = 'Оплаты чека по позициям';

UPDATE cl_moneybags ccr      
JOIN (SELECT      
    ip.MoneybagId,      
    SUM(ip.IPCount) AS MoneybagCount,      
    SUM(ip.IPSumCalc) AS MoneybagSum      
  FROM ch_item_moneybags ip      
  WHERE ip.ChekId = :ChekId      
  GROUP BY ip.MoneybagId) AS tmp2      
  ON tmp2.MoneybagId = ccr.MoneybagId      
SET ccr.TotalMoneybagCountRemains = ccr.TotalMoneybagCountRemains - tmp2.MoneybagCount,      
    ccr.TotalMoneybagSumRemains = ccr.TotalMoneybagSumRemains - tmp2.MoneybagSum,      
    ccr.LocalMoneybagCountRemains = ccr.LocalMoneybagCountRemains - tmp2.MoneybagCount,      
    ccr.LocalMoneybagSumRemains = ccr.LocalMoneybagSumRemains - tmp2.MoneybagSum,      
    ccr.TotalMoneybagCountSpend = ccr.TotalMoneybagCountSpend + tmp2.MoneybagCount,      
    ccr.TotalMoneybagSumSpend = ccr.TotalMoneybagSumSpend + tmp2.MoneybagSum,      
    ccr.LocalMoneybagCountSpend = ccr.LocalMoneybagCountSpend + tmp2.MoneybagCount,      
    ccr.LocalMoneybagSumSpend = ccr.LocalMoneybagSumSpend + tmp2.MoneybagSum      
WHERE ccr.ClientId = :ClientId; 

==========================================================================================================================================================================================================
|id |select_type  |table       |type   |possible_keys                                     |key                    |key_len |ref                    |rows  |Extra                                         |
==========================================================================================================================================================================================================
|1  |PRIMARY      |<derived2>  |ALL    |null                                              |null                   |null    |null                   |2     |Using where                                   |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|1  |PRIMARY      |ccr         |eq_ref |UK_ClientId_MoneybagId,ClientId,FK_cl_moneybags_re|UK_ClientId_MoneybagId |9       |const,tmp2.MoneybagId  |1     |                                              |
|   |             |            |       |f_Moneybags                                       |                       |        |                       |      |                                              |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|2  |DERIVED      |ip          |ref    |UK_ch_item_moneybags                              |UK_ch_item_moneybags   |8       |const                  |1     |Using where; Using temporary; Using filesort  |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

UPDATE cl_moneybags cm SET
  TotalMoneybagCountRemains = TotalMoneybagCountRemains - 
    (SELECT SUM(cim.IPCount) FROM ch_item_moneybags cim WHERE cim.ChekId = :ChekId AND cim.MoneybagId = cm.MoneybagId),
  TotalMoneybagSumRemains = TotalMoneybagSumRemains - 
    (SELECT SUM(cim.IPSumCalc) FROM ch_item_moneybags cim WHERE cim.ChekId = :ChekId AND cim.MoneybagId = cm.MoneybagId)
  -- аналогично для остальных полей
WHERE cm.ClientId = :ClientId