0

I Have closing balances in Akey table and i have pool id in cpp table i want to update value of Bppd key_amount column with akey closing_balance but before that i have to check the default_pool='ACTIVE' from table cpp

sample tables are mentioned

TABLE Akey (
  `id` int(11) NOT NULL,  
  `serial_key` varchar(82) DEFAULT NULL,
  `closing_balance` double(20,2) DEFAULT '0.00',
  `product_id` int(11) DEFAULT NULL,  
  PRIMARY KEY (`id`)
) 

TABLE Bppd(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pool_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `akey_id` int(11) DEFAULT NULL,
  `key_amount` double DEFAULT '0',
  `working_balance` double DEFAULT '0',
   PRIMARY KEY (`id`)
)

TABLE Cpp (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `akey_id` int(11) DEFAULT NULL,
  `pool_id` int(11) DEFAULT NULL,
  `default_pool` varchar(15) DEFAULT NULL,  
  PRIMARY KEY (`id`)
)

I have Following query

UPDATE `Bppd` ppd
    JOIN AKey kk ON ppd.`akey_id` = kk.`id`
    JOIN `Cpp` pp ON pp.`akey_id` = kk.`id` AND pp.`default_pool` = 'ACTIVE' AND ppd.`akey_id` = pp.`akey_id`
    SET ppd.`key_amount`= kk.`closing_balance`
Abdul
  • 577
  • 1
  • 5
  • 21

1 Answers1

0

Try this

UPDATE Bppd b INNER JOIN Akey a on a.id = b.akey_id AND a.product_id = b.product_id
INNER JOIN Cpp c ON b.key_id = c.akey_id AND b.pool_id = c.pool_id 
SET b.key_amount = a.closing_balance
WHERE c.default_pool='ACTIVE'

Or this:

UPDATE Bppd b SET key_amount = (SELECT closing_balance FROM Akey a WHERE  a.id = b.akey_id and a.product_id = b.product_id)
WHERE EXISTS (
 SELECT 1 FROM Cpp c
 WHERE b.akey_id = c.akey_id AND b.pool_id = c.pool_id 
 AND c.default_pool='ACTIVE' 
 ) 
 AND EXISTS (
  SELECT 1 FROM Akey a 
  WHERE a.id = b.akey_id and a.product_id = b.product_id
 )
rafalopez79
  • 2,046
  • 4
  • 27
  • 23