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 |