CODEWITHSUNDEEP
mysql
Patrick Simard
Patrick Simard

Reputation: 2375

UPDATE affecting 0 ROWS

I need to update all my invoices and regenerate the sub_total and total for each invoices. Unfortunatly, my query returns 0 affacted rows when I run it and I just don't get it. Theres no MySQL errors returned so the syntaxe passes ... But as you can see in the dump, the table is not empty and should have updated sous_total and total based on the FACTURE_ITEMS

My Query is:

UPDATE FACTURES f0 SET 
f0.sous_total=(
    SELECT SUM(f1.quantite*f1.prix) FROM FACTURES_ITEMS f1 WHERE f1.id_facture=f0.id
), 
f0.total= sous_total+(
        SELECT SUM(f2.taxes) FROM FACTURES_ITEMS f2 WHERE f2.id_facture=f0.id AND f2.taxable=1
)

My 2 tables looks like:

--
-- Table structure for table `FACTURES`
--

CREATE TABLE `FACTURES` (
  `id` int(10) UNSIGNED NOT NULL,
  `id_etablissement` int(10) UNSIGNED DEFAULT NULL,
  `id_evenement` int(10) UNSIGNED DEFAULT NULL,
  `id_demande` int(10) UNSIGNED DEFAULT NULL,
  `id_cotisation` int(10) UNSIGNED DEFAULT NULL,
  `id_facture_parent` int(10) UNSIGNED DEFAULT NULL,
  `numero_facture` int(9) NOT NULL,
  `personne_contact` varchar(255) NOT NULL,
  `courriel` varchar(60) NOT NULL,
  `nom` varchar(255) NOT NULL,
  `adresse` varchar(255) NOT NULL,
  `ville` varchar(255) NOT NULL,
  `province` varchar(35) DEFAULT NULL,
  `id_pays` int(10) UNSIGNED DEFAULT NULL,
  `code_postal` varchar(12) NOT NULL,
  `telephone` varchar(16) NOT NULL,
  `extension` varchar(25) DEFAULT NULL,
  `fax` varchar(16) NOT NULL,
  `id_statut` int(10) UNSIGNED NOT NULL,
  `passkey` varchar(255) NOT NULL,
  `creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sous_total` decimal(10,2) NOT NULL,
  `total_tps` decimal(10,2) NOT NULL,
  `total_tvq` decimal(10,2) NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `id_devise` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `FACTURES`
--

INSERT INTO `FACTURES` (`id`, `id_etablissement`, `id_evenement`, `id_demande`, `id_cotisation`, `id_facture_parent`, `numero_facture`, `personne_contact`, `courriel`, `nom`, `adresse`, `ville`, `province`, `id_pays`, `code_postal`, `telephone`, `extension`, `fax`, `id_statut`, `passkey`, `creation`, `sous_total`, `total_tps`, `total_tvq`, `total`, `id_devise`) VALUES
(1, 1387, 1, NULL, NULL, NULL, 1, '***', '***', '***', '***', '***', '***', 124, '***', '***', NULL, '', 4, '***', '2020-02-20 15:19:42', 2500.00, 0.00, 0.00, 0.00, 1);

-- --------------------------------------------------------

--
-- Table structure for table `FACTURES_ITEMS`
--

CREATE TABLE `FACTURES_ITEMS` (
  `id` int(10) UNSIGNED NOT NULL,
  `id_facture` int(10) UNSIGNED NOT NULL,
  `id_inscription` int(10) UNSIGNED DEFAULT NULL,
  `titre` varchar(255) DEFAULT NULL,
  `quantite` int(11) NOT NULL,
  `prix` decimal(10,2) NOT NULL,
  `taxes` decimal(10,2) NOT NULL,
  `id_statut` tinyint(3) UNSIGNED DEFAULT NULL,
  `taxable` int(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `FACTURES_ITEMS`
--

INSERT INTO `FACTURES_ITEMS` (`id`, `id_facture`, `id_inscription`, `titre`, `quantite`, `prix`, `taxes`, `id_statut`, `taxable`) VALUES
(1, 1, 1, NULL, 1, 500.00, 74.88, NULL, 1),
(2, 1, 2, NULL, 1, 500.00, 74.88, NULL, 1),
(3, 1, 3, NULL, 1, 500.00, 74.88, NULL, 1),
(4, 1, 4, NULL, 1, 500.00, 74.88, NULL, 1),
(5, 1, 5, NULL, 1, 500.00, 74.88, NULL, 1);

Upvotes: 2

Views: 125

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

This is a peculiarity of MySQL that surprises some people the first time they encounter it.

"Affected rows 0" is true if the values you are setting are already the values stored in those rows. That is, if there is no net change in the data, it is considered to be NOT "affected."

Upvotes: 2

Related Questions