CODEWITHSUNDEEP
sql-server
r3plica
r3plica

Reputation: 13367

SQL Update query using select statement

I am trying to update a column in a table where the another column matches and selecting the top 1 for that column as the value to update. Hard to explain, but this is what I wrote:

UPDATE CameraSpecifications AS a
SET a.Variant = (
SELECT TOP 1 GTIN
FROM CameraSpecifcations 
WHERE b.ModelGroup = a.ModelGroup )

Hopefully that explains what I am trying to do. I have a select statement that might also help:

SELECT 
    (
        SELECT TOP 1 b.GTIN
        FROM CameraSpecifications AS b
        WHERE b.ModelGroup = a.ModelGroup
    ) AS Gtin,
    a.ModelGroup, 
    COUNT(a.ModelGroup)
FROM CameraSpecifications AS a
GROUP BY a.ModelGroup

Upvotes: 0

Views: 57

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

We can try doing an update join from CameraSpecifications to a CTE which finds the top GTIN value for each model group. Note carefully that I use an ORDER BY clause in ROW_NUMBER. It makes no sense to use TOP 1 without ORDER BY, so you should at some point update your question and mention TOP 1 with regard to a certain column.

WITH cte AS (
    SELECT ModelGroup, GTIN,
        ROW_NUMBER() OVER (PARTITION BY ModelGroup ORDER BY some_col) rn
    FROM CameraSpecifications
)

UPDATE cs
SET Variant = t.GTIN
FROM CameraSpecifcations cs
INNER JOIN cte t
    ON cs.ModelGroup = t.ModelGroup
WHERE
    t.rn = 1;

Upvotes: 1

Related Questions