Adding rows to a table respecting the key columns structure

I have a very large table, which follows these structure (I past it here simplified):

Product	Line	Name	Quantity	Unit	Cost
Pepe	10000	Lucia	4	UD	8
Pepe	20000	Santiago	7	UD	5.5
Pepe	30000	Mariangeles	10	KG	6
Antonio	10000	Naiara	4	KG	8
Antonio	20000	Toni	7	KG	3
Vanesa	10000	Lucia	4	UD	8
Vanesa	20000	Santiago	7	KG	8
Vanesa	30000	Toni	10	KG	3
Vanesa	40000	Gines	4	KG	8

I need to add a new Name (Dany) to every Product, so the table looks like this:

Name: Dany; Quantity:15; Unit: L; Cost: 2

The result would be:

Product	Line	Name	Quantity	Unit	Cost
Pepe	10000	Lucia	4	UD	8
Pepe	20000	Santiago	7	UD	5.5
Pepe	30000	Mariangeles	10	KG	6
Pepe	40000	Dany	15	L	2
Antonio	10000	Naiara	2	KG	8
Antonio	20000	Toni	7	KG	3
Antonio	30000	Dany	15	L	2
Vanesa	10000	Lucia	4	UD	8
Vanesa	20000	Santiago	7	KG	8
Vanesa	30000	Toni	10	KG	3
Vanesa	40000	Gines	4	KG	8
Vanesa	50000	Dany	15	L	2

The fact is that the ‘line’ has to be the subsequent number of the existing (for example, if the last Name of a product is 30000 (see Pepe), Dany’s row line has to be 40000, if the last Name of a product is 20000, Dany’s row line has to be 30000 (see Antonio)).

I can do it using SQL (DBeaver or Microsoft Access) or R, I am thinking about an R loop or a SQL complex query with count(), but some help would be very appreciated.

Thank you a lot.

Answer

You would insert the value into the table using insert:

insert into t (Product, Line, Name, Quantity, Unit, Cost)
    select product, max(line) + 10000, 'Dany', 15, 'L', 2
    from t
    group by product;

If you don’t want to modify the table but just want to see the data, you can run a query:

select Product, Line, Name, Quantity, Unit, Cost
from t
union all
select product, max(line) + 10000, 'Dany', 15, 'L', 2
from t
group by product