I'm practicing SQL and trying to get the last record and add a new record that's incrementing the year and id. I know this can be done easier but I'm trying to get the ideas of how to use CTE. Thank you in advance.
I'm getting a syntax error. using MySQL 8.0
WITH cte_0 (id,first_name,last_name,age,date_of_age) as (
select * from sales order by age desc limit 1
),
cte_1 (id,first_name,last_name,age,data_of_age) as (
select id + 1 as id,first_name,last_name,age +1 as age, DATE_ADD(date_of_age ,INTERVAL 1 YEAR) as data_of_age from cte_0
)
insert into sales (id,first_name,last_name,age,data_of_age) select id,first_name,last_name,age,data_of_age from cte_1
;
SYNTAX ERROR: Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into sales (id,first_name,last_name,age,data_of_age) select id,first_name' at line 7 0.000 sec
You should use the CTEs inside the INSERT statement:
insert into sales (id,first_name,last_name,age,data_of_age)
WITH cte_0 (id,first_name,last_name,age,date_of_age) as (
select * from sales order by age desc limit 1
),
cte_1 (id,first_name,last_name,age,data_of_age) as (
select id + 1 as id,first_name,last_name,age +1 as age, DATE_ADD(date_of_age ,INTERVAL 1 YEAR) as data_of_age from cte_0
)
select id,first_name,last_name,age,data_of_age from cte_1;