SQL Server How to UPDATE with SELECT and GROUP BY clause

Our team uses SQL Server and I wanted to use aggregate function to update one of the columns for the NEW values that are being inserted existing table.

Below is the existing table and values in it.

CREATE TABLE CARS 
(
   ID INT NOT NULL,
   carCompany VARCHAR(100) NOT NULL,
   feature VARCHAR(100) NOT NULL,
   sequence INT NOT NULL
);

INSERT INTO CARS VALUES (1, 'Toyota', 'Auto transmission', 1); 
INSERT INTO CARS VALUES (2, 'BMW', 'Keyless Entry system', 1);
INSERT INTO CARS VALUES (3, 'Toyota', 'Power Steering', 2); 
INSERT INTO CARS VALUES (4, 'Tesla', 'Auto transmission', 1); 
INSERT INTO CARS VALUES (5, 'BMW', 'Auto transmission', 2);
INSERT INTO CARS VALUES (6, 'Tesla', 'Keyless Entry system', 2);
INSERT INTO CARS VALUES (7, 'BMW', 'Power Steering', 3); 

Requirement came in to ‘Auto Pilot’ for BMW and Tesla only with sequence column incremented in the same carCompany group. Since, Insert statement was easier, I just inserted records and struggling to get the Update statement right.

INSERT INTO CARS VALUES (8, 'Tesla', 'Auto Pilot', 0);
INSERT INTO CARS VALUES (9, 'BMW', 'Auto Pilot', 0);

Below UPDATE statement is INCORRECT. Kindly help here.

UPDATE c1 
SET c1.sequence = d.sq
FROM 
    (SELECT MAX(c2.sequence) + 1 AS sq 
     FROM CARS c2 
     WHERE c2.carCompany = c1.carCompany 
     GROUP BY c2.carCompany) d
WHERE c1.sequence = 0

Answer

You’re close, just move the correlated subquery into the SET statement:

UPDATE c1 
    SET c1.sequence = (SELECT max(fc2.sequence) + 1 as sq FROM CARS c2 WHERE c2.carCompany = c1.carCompany GROUP BY c2.carCompany)
FROM CARS c1
WHERE c1.sequence = 0

But note that this only works if there’s only one new record for that car company. I.e., if you had two new Tesla features they’d both be set to 2. You can use a CTE with the RANK function to allow for any number of new values:

WITH NewSequences
AS (
SELECT id, RANK()OVER(PARTITION BY c1.carCompany ORDER BY id) + (SELECT max(fc2.sequence) + 1 as sq FROM CARS c2 WHERE c2.carCompany = c1.carCompany GROUP BY c2.carCompany) AS NewSeq
FROM CARS
WHERE c1.[sequence] = 0
)
UPDATE c1 
    SET c1.sequence = n.NewSeq
FROM CARS c1
    INNER JOIN NewSequences n
        ON c1.id = NewSequences.id