CODEWITHSUNDEEP
mysqlsql
Андрей Вячеславович
Андрей Вячеславович

Reputation: 1

Cte blocks sql insert

My code doesn't work if i have insertion in it. Otherwise if i delete insertion part it would work fine (just selection) Also deleting cte helps to avoid with insert but separately


WITH temp_cte as (select
     item_id AS item_id,
     item_name AS item_name,
     item_price AS item_price,
     date(created_dttm) AS valid_from_dt,
     LEAD(date(created_dttm), 1, '9999-12-31') 
     OVER (PARTITION BY item_id ORDER BY date(created_dttm)) as next_price
     from item_prices)
      

     insert into dict_item_prices (item_id, item_name, item_price, valid_from_dt, valid_to_dt)
     
     
      SELECT item_id,
          item_name,
          item_price,
          valid_from_dt,
            (CASE  next_price
            WHEN '9999-12-31' THEN '9999-12-31'
            ELSE DATE_ADD(next_price,interval -1 day)
            END) as valid_to_dt
      from temp_cte

      select * from dict_item_prices;

even if I don't use my cte it's still fail

Upvotes: 0

Views: 69

Answers (1)

nbk
nbk

Reputation: 49395

The WITH clause is part if the select, so your query looks like

 insert into dict_item_prices (item_id, item_name, item_price, valid_from_dt, valid_to_dt)
WITH temp_cte as (select
     item_id AS item_id,
     item_name AS item_name,
     item_price AS item_price,
     date(created_dttm) AS valid_from_dt,
     LEAD(date(created_dttm), 1, '9999-12-31') 
     OVER (PARTITION BY item_id ORDER BY date(created_dttm)) as next_price
     from item_prices)     
     
      SELECT item_id,
          item_name,
          item_price,
          valid_from_dt,
            (CASE  next_price
            WHEN '9999-12-31' THEN '9999-12-31'
            ELSE DATE_ADD(next_price,interval -1 day)
            END) as valid_to_dt
      from temp_cte;

  select * from dict_item_prices;

Upvotes: 1

Related Questions