MySQL Update query not giving expected result

[Gideon Engelbrecht]

I have the following MySQL update statement :

UPDATE
penalisasietrash As a
JOIN
parsreelstrash As b On (a.Kult = b.Kultivar And a.KlasBeskrywing = b.Klasbeskrywing)
Set
a.Parsreels_Suiker =
Case
When b.Aangepas = 'JA' AND a.Datum >= b.DatumAangepas THEN b.Suiker_onder_Aangepas
When b.Aangepas2 = 'JA' AND a.Datum >= b.DatumAangepas2 THEN b.Suiker_onder_Aangepas2
ELSE Suiker_onder
END ;


However the second where does notseem to execute.

Here is the create statement for parsreelstrash :

CREATE TABLE `parsreelstrash` (
`ParsreelsID` int NOT NULL AUTO_INCREMENT,
`Kultivar` text,
`Klasbeskrywing` text,
`Suiker_onder` decimal(10,2) DEFAULT NULL,
`DatumAangepas` date DEFAULT NULL,
`Suiker_onder_Aangepas` decimal(10,2) DEFAULT NULL,
`Aangepas` varchar(5) DEFAULT NULL,
`DatumAangepas2` date DEFAULT NULL,
`Suiker_onder_Aangepas2` decimal(10,2) DEFAULT NULL,
`Aangepas2` varchar(5) DEFAULT NULL,
PRIMARY KEY (`ParsreelsID`),
UNIQUE KEY `ParsreelsID_UNIQUE` (`ParsreelsID`)
) ENGINE=InnoDB AUTO_INCREMENT=185 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


And some data :

INSERT INTO `parsreelstrash` (`ParsreelsID`,`Kultivar`,`Klasbeskrywing`,`Suiker_onder`,`DatumAangepas`,`Suiker_onder_Aangepas`,`Aangepas`,`DatumAangepas2`,`Suiker_onder_Aangepas2`,`Aangepas2`) VALUES (178,'COL','BULK',20.00,'2022-03-31',19.00,'JA','2022-04-06',0.00,'JA');
INSERT INTO `parsreelstrash` (`ParsreelsID`,`Kultivar`,`Klasbeskrywing`,`Suiker_onder`,`DatumAangepas`,`Suiker_onder_Aangepas`,`Aangepas`,`DatumAangepas2`,`Suiker_onder_Aangepas2`,`Aangepas2`) VALUES (179,'COL','KULTIVAR',21.00,'2022-03-31',19.00,'JA','2022-04-06',0.00,'JA');
INSERT INTO `parsreelstrash` (`ParsreelsID`,`Kultivar`,`Klasbeskrywing`,`Suiker_onder`,`DatumAangepas`,`Suiker_onder_Aangepas`,`Aangepas`,`DatumAangepas2`,`Suiker_onder_Aangepas2`,`Aangepas2`) VALUES (180,'COL','FAIRTRADE',20.00,NULL,NULL,'NEE','2022-04-06',0.00,'JA');
INSERT INTO `parsreelstrash` (`ParsreelsID`,`Kultivar`,`Klasbeskrywing`,`Suiker_onder`,`DatumAangepas`,`Suiker_onder_Aangepas`,`Aangepas`,`DatumAangepas2`,`Suiker_onder_Aangepas2`,`Aangepas2`) VALUES (181,'COL','KONTRAK',20.00,NULL,NULL,'NEE','2022-04-06',0.00,'JA');


The create statement for penalisasietrash :

CREATE TABLE `penalisasietrash` (
`Aliasnaam` varchar(45) DEFAULT NULL,
`Datum` date DEFAULT NULL,
`Kult` text,
`KlasBeskrywing` varchar(45) DEFAULT NULL,
`Parsreels_Suiker` decimal(20,10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

And some data :


INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (1,'JOHAN COETZEE','2022-03-14','COL','BULK',20.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (12,'JOHAN COETZEE','2022-03-16','COL','KULTIVAR',21.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (19,'JOHAN COETZEE','2022-03-22','COL','BULK',20.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (20,'JOHAN COETZEE','2022-03-23','COL','KULTIVAR',21.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (28,'JOHAN COETZEE','2022-03-23','COL','BULK',20.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (31,'JOHAN COETZEE','2022-03-24','COL','BULK',20.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (33,'JOHAN COETZEE','2022-03-25','COL','KULTIVAR',21.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (36,'JOHAN COETZEE','2022-03-25','COL','BULK',20.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (38,'JOHAN COETZEE','2022-03-31','COL','BULK',19.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (41,'JOHAN COETZEE','2022-03-31','COL','BULK',19.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (42,'JOHAN COETZEE','2022-03-31','COL','KULTIVAR',19.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (44,'JOHAN COETZEE','2022-04-06','COL','KULTIVAR',19.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (68,'JOHAN COETZEE','2022-04-07','COL','KULTIVAR',19.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (70,'JOHAN COETZEE','2022-04-07','COL','BULK',19.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (82,'JOHAN COETZEE','2022-03-14','COL','BULK',20.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (92,'JOHAN COETZEE','2022-03-16','COL','BULK',20.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (99,'JOHAN COETZEE','2022-03-16','COL','KULTIVAR',21.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (100,'JOHAN COETZEE','2022-03-22','COL','BULK',20.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (101,'JOHAN COETZEE','2022-03-23','COL','KULTIVAR',21.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (109,'JOHAN COETZEE','2022-03-23','COL','BULK',20.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (110,'JOHAN COETZEE','2022-03-23','COL','KULTIVAR',21.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (113,'JOHAN COETZEE','2022-03-24','COL','BULK',20.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (118,'JOHAN COETZEE','2022-03-25','COL','KULTIVAR',21.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (143,'JOHAN COETZEE','2022-04-06','COL','KULTIVAR',19.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (144,'JOHAN COETZEE','2022-04-07','COL','KULTIVAR',19.0000000000);
INSERT INTO `penalisasietrash` (`ID`,`Aliasnaam`,`Datum`,`Kult`,`KlasBeskrywing`,`Parsreels_Suiker`) VALUES (162,'JOHAN COETZEE','2022-04-07','COL','BULK',19.0000000000);


When I execute the following statement, I get an unexpected result :

select * from penalisasietrash
order by datum,kult, klasbeskrywing

The following table is the result :




+-----+---------------+------------+------+----------------+------------------+
| ID | Aliasnaam | Datum | Kult | KlasBeskrywing | Parsreels_Suiker |
+-----+---------------+------------+------+----------------+------------------+
| 1 | JOHAN COETZEE | 2022-03-14 | COL | BULK | 20.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 82 | JOHAN COETZEE | 2022-03-14 | COL | BULK | 20.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 92 | JOHAN COETZEE | 2022-03-16 | COL | BULK | 20.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 12 | JOHAN COETZEE | 2022-03-16 | COL | KULTIVAR | 21.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 99 | JOHAN COETZEE | 2022-03-16 | COL | KULTIVAR | 21.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 19 | JOHAN COETZEE | 2022-03-22 | COL | BULK | 20.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 100 | JOHAN COETZEE | 2022-03-22 | COL | BULK | 20.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 28 | JOHAN COETZEE | 2022-03-23 | COL | BULK | 20.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 109 | JOHAN COETZEE | 2022-03-23 | COL | BULK | 20.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 20 | JOHAN COETZEE | 2022-03-23 | COL | KULTIVAR | 21.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 101 | JOHAN COETZEE | 2022-03-23 | COL | KULTIVAR | 21.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 110 | JOHAN COETZEE | 2022-03-23 | COL | KULTIVAR | 21.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 31 | JOHAN COETZEE | 2022-03-24 | COL | BULK | 20.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 113 | JOHAN COETZEE | 2022-03-24 | COL | BULK | 20.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 36 | JOHAN COETZEE | 2022-03-25 | COL | BULK | 20.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 33 | JOHAN COETZEE | 2022-03-25 | COL | KULTIVAR | 21.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 118 | JOHAN COETZEE | 2022-03-25 | COL | KULTIVAR | 21.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 38 | JOHAN COETZEE | 2022-03-31 | COL | BULK | 19.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 41 | JOHAN COETZEE | 2022-03-31 | COL | BULK | 19.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 42 | JOHAN COETZEE | 2022-03-31 | COL | KULTIVAR | 19.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 44 | JOHAN COETZEE | 2022-04-06 | COL | KULTIVAR | 19.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 143 | JOHAN COETZEE | 2022-04-06 | COL | KULTIVAR | 19.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 70 | JOHAN COETZEE | 2022-04-07 | COL | BULK | 19.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 162 | JOHAN COETZEE | 2022-04-07 | COL | BULK | 19.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 68 | JOHAN COETZEE | 2022-04-07 | COL | KULTIVAR | 19.0000000000 |
+-----+---------------+------------+------+----------------+------------------+
| 144 | JOHAN COETZEE | 2022-04-07 | COL | KULTIVAR | 19.0000000000 |
+-----+---------------+------------+------+----------------+------------------+


What I am expecting is for the last six rows Parsreels_Suiker should be 0 - as datum >= '2022-04-06'
as it is in parsreelstrash.

Any help would be much appreciated.

Regards