NickName:Srinivas Lakshman Ask DateTime:2021-02-19T08:22:37

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

Copyright Notice：Content Author:「Srinivas Lakshman」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/66270101/sql-server-how-to-update-with-select-and-group-by-clause

Answers

Russell Fox 2021-02-19T00:36:14

You're close, just move the correlated subquery into the SET statement:\nUPDATE c1 \n SET c1.sequence = (SELECT max(fc2.sequence) + 1 as sq FROM CARS c2 WHERE c2.carCompany = c1.carCompany GROUP BY c2.carCompany)\nFROM CARS c1\nWHERE c1.sequence = 0\n\nBut 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:\nWITH NewSequences\nAS (\nSELECT 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\nFROM CARS\nWHERE c1.[sequence] = 0\n)\nUPDATE c1 \n SET c1.sequence = n.NewSeq\nFROM CARS c1\n INNER JOIN NewSequences n\n ON c1.id = NewSequences.id\n",