There are 2 tables: the first

CREATE TABLE `order` ( `idorder` INT(11) NOT NULL AUTO_INCREMENT, `create_date` DATETIME NOT NULL, `amount` VARCHAR(20) NOT NULL DEFAULT '0', `status` ENUM('processing','declined','approved','transfering') NOT NULL DEFAULT 'processing', `user_wallet` VARCHAR(50) NOT NULL DEFAULT 'User_wallet_is_unknown', `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`idorder`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=21 ;

second:

 CREATE TABLE `orders` ( `idorders` INT(11) NOT NULL AUTO_INCREMENT, `order_idorder` INT(11) NOT NULL, `order_date` VARCHAR(45) NULL DEFAULT NULL, `status` ENUM('Processing','Declined','Approved') NULL DEFAULT NULL, `secret_code` VARCHAR(45) NULL DEFAULT NULL, `wallets_idwallets` INT(11) NOT NULL, `user_wallet` VARCHAR(45) NULL DEFAULT NULL, `comment` VARCHAR(45) NULL DEFAULT NULL, `sum_to_buyer` FLOAT NULL DEFAULT NULL, `sum_for_us` FLOAT NULL DEFAULT NULL, `create_time` DATETIME NULL DEFAULT NULL, `paid_status` ENUM('paid','unpaid') NOT NULL DEFAULT 'unpaid', `paid_wallet` VARCHAR(225) NOT NULL DEFAULT 'unknowWallet', `paid_sum` VARCHAR(20) NOT NULL DEFAULT '0', PRIMARY KEY (`idorders`), INDEX `fk_orders_wallets_idx` (`wallets_idwallets`), INDEX `order_idorder` (`order_idorder`), CONSTRAINT `FK_orders_order` FOREIGN KEY (`order_idorder`) REFERENCES `order` (`idorder`) ON UPDATE NO ACTION ON DELETE NO ACTION, CONSTRAINT `fk_orders_wallets` FOREIGN KEY (`wallets_idwallets`) REFERENCES `wallets` (`idwallets`) ON UPDATE NO ACTION ON DELETE NO ACTION ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=106 ;

I do UPDATE request:

 UPDATE `order` SET `status` = 'transfering' WHERE `idorder` = ( SELECT `idorder` FROM ( SELECT `o`.`idorder`, `o`.`amount`, ( SELECT SUM(`sum_for_us`) FROM `orders` AS os WHERE `os`.`order_idorder` = `idorder` AND `os`.`paid_status` = 'paid' AND `os`.`status` = 'processing' ) as `paid_sum` FROM `order` AS o )a WHERE `paid_sum` = `amount` )

if 1 value is returned, then UPDATE works as it should, but it happens that more than one order and then UPDATE does not work anymore, tell me how to solve this kind of problem? You can choose values ​​first and then UPDATE in PHP to run, or maybe Can I solve this in the request to the database?

    2 answers 2

    If you always want to select only 1 result then add LIMIT 1 to the select.

    If you need to update all the lines that are selected select then replace WHERE idorder=(<select here>) with WHERE idorder IN (<select here>) .

      You need to write down the answer first, and then send all