Using Cte in MySQL
I have used the collowing query with success in SQL, but struggling to get it to work in MySQL.
What I trying to do is get the last three dates per plaasblok column and update the table.
with cte as(select *, row_number() over(partition by plaasblok order by date desc) rn
from ontledingsvyfjaarhistories where year(date) = year(getdate())) update t set last = c1.date, secondlast = c2.date, thirdlast = c3.date from ontledingsvyfjaarhistories
t left join cte c1 on t.plaasblok = c1.plaasblok and c1.rn = 1 left join cte c2 on t.plaasblok = c2.plaasblok and c2.rn = 2 left join cte c3 on t.plaasblok = c3.plaasblok and c3.rn = 3
I get an error "from" not valid at this position expectong EOF, ';"
My create statement is :
CREATE TABLE `ontledingsvyfjaarhistories` (
`ID` int NOT NULL AUTO_INCREMENT,
`LidNo` varchar(45) DEFAULT NULL,
`Lidnaam` varchar(45) DEFAULT NULL,
`PlaasNo` varchar(45) DEFAULT NULL,
`Plaasnaam` varchar(45) DEFAULT NULL,
`BlokNo` varchar(45) DEFAULT NULL,
`Kultivar` varchar(45) DEFAULT NULL,
`Alias` varchar(45) DEFAULT NULL,
`Aliasnaam` varchar(45) DEFAULT NULL,
`Date` date DEFAULT NULL,
`Tyd` time DEFAULT NULL,
`Suiker` decimal(10,2) DEFAULT NULL,
`pH` decimal(10,2) DEFAULT NULL,
`Suur` decimal(10,2) DEFAULT NULL,
`TipeOntleding` varchar(45) DEFAULT NULL,
`Jaar` int DEFAULT NULL,
`Last` date DEFAULT NULL,
`SecondLast` date DEFAULT NULL,
`ThirdLast` date DEFAULT NULL,
`Plaasblok` varchar(100) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID_UNIQUE` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=59863 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
And here is some data:
ID,LidNo,Lidnaam,PlaasNo,Plaasnaam,BlokNo,Kultivar,Alias,Aliasnaam,Date,Tyd,Suiker,pH,Suur,TipeOntleding,Jaar,Last,SecondLast,ThirdLast,Plaasblok
2,1815-MON,NULL,11332000,MONOTONKA,1-MON,PIN,PIERET,"PIETER RETIEF",2016-02-11,09:50:00,21.90,3.38,7.78,M,NULL,NULL,NULL,NULL,113320001-MON
3,1815-MON,NULL,11332000,MONOTONKA,1-MON,PIN,PIERET,"PIETER RETIEF",2017-02-01,09:41:00,21.10,3.21,8.23,M,NULL,NULL,NULL,NULL,113320001-MON
4,1815-MON,NULL,11332000,MONOTONKA,1-MON,PIN,PIERET,"PIETER RETIEF",2017-02-09,17:31:00,24.60,3.44,6.56,M,NULL,NULL,NULL,NULL,113320001-MON
5,1815-MON,NULL,11332000,MONOTONKA,1-MON,PIN,PIERET,"PIETER RETIEF",2020-03-04,12:01:00,30.20,3.82,6.58,M,NULL,NULL,NULL,NULL,113320001-MON
6,1815-MON,NULL,11332000,MONOTONKA,10-MON,COL,PIERET,"PIETER RETIEF",2016-02-16,15:39:00,18.50,2.87,11.87,M,NULL,NULL,NULL,NULL,1133200010-MON
7,1815-MON,NULL,11332000,MONOTONKA,10-MON,COL,PIERET,"PIETER RETIEF",2018-02-28,11:58:00,23.50,3.08,10.42,M,NULL,NULL,NULL,NULL,1133200010-MON
8,1815-MON,NULL,11332000,MONOTONKA,10-MON,COL,PIERET,"PIETER RETIEF",2019-02-26,12:12:00,18.60,3.15,11.91,M,NULL,NULL,NULL,NULL,1133200010-MON
10,1815-MON,NULL,11332000,MONOTONKA,1-MON,PIN,PIERET,"PIETER RETIEF",2016-02-11,09:50:00,21.90,3.38,7.78,M,NULL,NULL,NULL,NULL,113320001-MON
11,1815-MON,NULL,11332000,MONOTONKA,1-MON,PIN,PIERET,"PIETER RETIEF",2017-02-01,09:41:00,21.10,3.21,8.23,M,NULL,NULL,NULL,NULL,113320001-MON
12,1815-MON,NULL,11332000,MONOTONKA,1-MON,PIN,PIERET,"PIETER RETIEF",2017-02-09,17:31:00,24.60,3.44,6.56,M,NULL,NULL,NULL,NULL,113320001-MON
13,1815-MON,NULL,11332000,MONOTONKA,1-MON,PIN,PIERET,"PIETER RETIEF",2020-03-04,12:01:00,30.20,3.82,6.58,M,NULL,NULL,NULL,NULL,113320001-MON
14,1815-MON,NULL,11332000,MONOTONKA,10-MON,COL,PIERET,"PIETER RETIEF",2016-02-16,15:39:00,18.50,2.87,11.87,M,NULL,NULL,NULL,NULL,1133200010-MON
15,1815-MON,NULL,11332000,MONOTONKA,10-MON,COL,PIERET,"PIETER RETIEF",2018-02-28,11:58:00,23.50,3.08,10.42,M,NULL,NULL,NULL,NULL,1133200010-MON
16,1815-MON,NULL,11332000,MONOTONKA,10-MON,COL,PIERET,"PIETER RETIEF",2019-02-26,12:12:00,18.60,3.15,11.91,M,NULL,NULL,NULL,NULL,1133200010-MON
17,1815-MON,NULL,11332000,MONOTONKA,12-MON,CHB,PIERET,"PIETER RETIEF",2017-02-21,08:20:00,20.00,3.11,9.26,M,NULL,NULL,NULL,NULL,1133200012-MON
18,1815-MON,NULL,11332000,MONOTONKA,12-MON,CHB,PIERET,"PIETER RETIEF",2018-02-14,16:16:00,18.70,3.09,11.78,M,NULL,NULL,NULL,NULL,1133200012-MON
19,1815-MON,NULL,11332000,MONOTONKA,12-MON,CHB,PIERET,"PIETER RETIEF",2020-02-19,14:39:00,22.00,3.22,8.42,M,NULL,NULL,NULL,NULL,1133200012-MON
As a newby maybe CTE is above me. Could the be another way to get the last three date per group and update the table with these values for each row?
Regards