MySQL 8.0

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

create table customers(
  customer_id int,
  firstname  varchar(20),  
  surname varchar(20)
);
insert into customers (customer_id,   firstname,   surname)
values
( 1,' jim  ','sock'),
( 2,' sue  ','sock'),
( 3,'brad  ','sock'),
( 4,'tony  ','sock'),
( 5,'paul  ','sock'),
( 6,'betty ','sock'),
( 7,'allen ','sock'),
( 8,'rita  ','sock'),
( 9,' pam  ','sock'),
(10,' deb  ','sock')
;

Records: 10  Duplicates: 0  Warnings: 0


INSERT INTO customers(customer_id, firstname, surname)
SELECT  MAX(customer_id) over()
   + row_number() over(order by customer_id), firstname, 'shoe' 
FROM customers;

Records: 10  Duplicates: 0  Warnings: 0


select * from customers;


customer_id
firstname
surname


1
 jim
sock

2
 sue
sock

3
brad
sock

4
tony
sock

5
paul
sock

6
betty
sock

7
allen
sock

8
rita
sock

9
 pam
sock

10
 deb
sock

11
 jim
shoe

12
 sue
shoe

13
brad
shoe

14
tony
shoe

15
paul
shoe

16
betty
shoe

17
allen
shoe

18
rita
shoe

19
 pam
shoe

20
 deb
shoe


SET @m = (SELECT MAX(customer_id) FROM customers);
INSERT INTO customers(customer_id, firstname, surname)
SELECT @m
   + row_number() over(order by customer_id), firstname, 'tie' 
FROM customers
WHERE surname = 'sock';

Records: 10  Duplicates: 0  Warnings: 0


select * from customers;


customer_id
firstname
surname


1
 jim
sock

2
 sue
sock

3
brad
sock

4
tony
sock

5
paul
sock

6
betty
sock

7
allen
sock

8
rita
sock

9
 pam
sock

10
 deb
sock

11
 jim
shoe

12
 sue
shoe

13
brad
shoe

14
tony
shoe

15
paul
shoe

16
betty
shoe

17
allen
shoe

18
rita
shoe

19
 pam
shoe

20
 deb
shoe

21
 jim
tie

22
 sue
tie

23
brad
tie

24
tony
tie

25
paul
tie

26
betty
tie

27
allen
tie

28
rita
tie

29
 pam
tie

30
 deb
tie

customer_id	firstname	surname
1	jim	sock
2	sue	sock
3	brad	sock
4	tony	sock
5	paul	sock
6	betty	sock
7	allen	sock
8	rita	sock
9	pam	sock
10	deb	sock
11	jim	shoe
12	sue	shoe
13	brad	shoe
14	tony	shoe
15	paul	shoe
16	betty	shoe
17	allen	shoe
18	rita	shoe
19	pam	shoe
20	deb	shoe