SQL Insert with max ID + 1 (In-Depth)

Question

I wanted to further elaborate on a question being posed here- Insert and set value with max()+1 problems

Say we start with the same scenario SOLUTION:

INSERT INTO 
customers( customer_id, firstname, surname )
SELECT MAX( customer_id ) + 1, 'jim', 'sock' FROM customers

Let's also say that the whole Sock family are customers at this shop. For the sake of the question, let's say there are 10 unique firstname's all with their own unique customer_id's as well, BUT they all have the same surname 'sock'.

What I want to do is say Big Papa Sock comes in and wants to replicate his whole family under his new last name ('shoe'), and for each entry we need a new unique customer_id (that is +1 of the highest current customer_id).

So the data we currently have in a mock customers table is-

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

And I want to insert the new 'shoe' data to get a new customers table-

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

So essentially what I need it to do is replicate each combination of unique entries and insert into customers. So it should keep the firstname the same, but add a new entry based on the max customer_id+1 and the new surname, 'shoe'

What I have tried so far is-

INSERT INTO 
customers(customer_id, firstname, surname) 
SELECT MAX(customer_id) + 1, firstname, 'shoe' FROM customers 
WHERE surname = 'sock'

But there seems to be an error when inserting.

Any help would be greatly appreciated.

Fix your data model to use an auto incremented column. That is the correct way to handle this. — Gordon Linoff, Jun 11 '21 at 16:34
This would be a perfect solution, but for the sake of the argument, I'd still want to find a way to code it manually — Delto, Jun 11 '21 at 17:02
@GordonLinoff could you give an advice how to solve this by auto incremented column when using multiple fields as primary key. I have orderId and order item (as in the position of the list). The order item should be an incremented value, based on the orderId e.g.: inserting in the table with the same orderId should auto increment the order item of the same orderId. Couldn't find a solution for this besides using triggers so far and would appreciate any suggestion to solve this. — Sercan Samet Savran, Mar 29 '23 at 01:26

Serg · Accepted Answer · 2021-06-11T19:23:27.240

1

If an auto incremented column is not an option for a reason, try

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

This is subject to race condition, you know.

EDIT

Another option just find max first

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';

db<>fiddle

edited Jun 11 '21 at 19:23

answered Jun 11 '21 at 17:13

Serg

22,285
5
21
48

How would this work if there were other entries not with the last name 'sock'? I don't see anywhere that indicates that as the surname value – Delto Jun 11 '21 at 18:40
If only one special last name is needed just add where. Otherwise refine the question how other surnames should be processed. Is there a table of surname renames or something? – Serg Jun 11 '21 at 19:04
I just want to know if it were a larger dataset than what I gave, then where would we specify the surname that we are duplicating? Like what if the original dataset has 2 different last names ```'sock'``` and ```'feet'```. How would I be able to insert new duplicates from specifically ```'sock'``` with unique ```'customer_id'```'s? – Delto Jun 11 '21 at 19:13
@Serg - I like your answer, but you could've also done INSERT INTO customers(customer_id, firstname, surname) SELECT MAX(customer_id) over() + customer_id, firstname, 'shoe' FROM customers; Is there a real need to use row_number? – Rajeev Pande Jun 13 '21 at 08:28
@RajeevPande, yes `customer_id` instead of `row_number()..` should be OK because customer_id is unique. – Serg Jun 13 '21 at 11:16

SQL Insert with max ID + 1 (In-Depth)

1 Answers1