MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


CREATE TABLE table1 (
     customer_id CHAR(5), 
     product varchar(20), 
     order_date_id char(8), 
     first_product varchar(20) null);
INSERT INTO table1 VALUES
    ('C0001','apple','20200224',NULL),
    ('C0001','pear','20220101',NULL),
    ('C0002','strawberry','20200224',NULL),
    ('C0001','apple','20200226',NULL);
SELECT
   t.*,
   ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_id, order_date_id) AS first_occurrance
FROM table1 t;


customer_id
product
order_date_id
first_product
first_occurrance


C0001
apple
20200224
null
1

C0001
apple
20200226
null
2

C0001
pear
20220101
null
3

C0002
strawberry
20200224
null
1


SELECT
   t.customer_id,
   MIN(t.order_date_id) as min_order_date
FROM table1 t
GROUP BY t.customer_id
;


customer_id
min_order_date


C0001
20200224

C0002
20200224


UPDATE table1 
JOIN (
   SELECT
      t.customer_id,
      MIN(t.order_date_id) as min_order_date
   FROM table1 t
   GROUP BY t.customer_id
) fp ON fp.customer_id = table1.customer_id
JOIN table1 t2 on t2.customer_id = table1.customer_id 
              AND t2.order_date_id = fp.min_order_date
SET table1.first_product = t2.product;
SELECT *
FROM table1;


customer_id
product
order_date_id
first_product


C0001
apple
20200224
apple

C0001
pear
20220101
apple

C0002
strawberry
20200224
strawberry

C0001
apple
20200226
apple


-- back to original data
UPDATE table1 set first_product = null;
UPDATE table1
JOIN (
   SELECT
      t.*,
      ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_id, order_date_id) AS first_occurrance
   FROM table1 t
) fp on fp.customer_id = table1.customer_id and fp.first_occurrance = 1
set table1.first_product = fp.product;
SELECT *
FROM table1;


customer_id
product
order_date_id
first_product


C0001
apple
20200224
apple

C0001
pear
20220101
apple

C0002
strawberry
20200224
strawberry

C0001
apple
20200226
apple

customer_id	product	order_date_id	first_product	first_occurrance
C0001	apple	20200224	null	1
C0001	apple	20200226	null	2
C0001	pear	20220101	null	3
C0002	strawberry	20200224	null	1