I have 2 tables (tables: visit and basket) and I need a way to insert data into table basket with some data from table shopping.

table: visit

bas_ID is the same ID as vis_ID. I want to insert into basket for all visits >= '2021-05-01' the item 'freebie' with the same visit date 'vis_date' to field basket date 'bas_date'

My problem is, if there were items in basket, the next basket position 'bas_pos' should be the max value 1. But if there was no basket item in table basket, the bas_pos should 1.

output of table: basket

I think I have to implement with subselect? Is my code right for DB2? Thank you.

Insert into basket
  select vis_ID, 'freebie', vis_date, case when(select max(bas_pos) from basket b
                                       where b.bas_ID = c.vis_ID) is null then '1' else int(select vis_ID, 'freebie', vis_date, case when(select max(bas_pos) from basket b
                                       where b.bas_ID = c.vis_ID)  1 end
  from visit c
  where vis_date >= '2021-05-01'

CodePudding user response：

Try this:

Insert into basket (bas_ID, bas_item, bas_date, pas_pos)
select 
  c.vis_ID, 'freebie', c.vis_date
, coalesce (b.bas_pos, 0)   rn_
from
(
  select 
    vis_ID, vis_date
  , row_number () over (partition by vis_id) as rn_
  from visit
  where vis_date >= '2021-05-01'
) c
left join 
(
  select bas_ID, max (pas_pos) as bas_pos
  from basket 
  group by bas_ID
) b on b.bas_ID = c.vis_ID