SQL Update query using select statement

Question

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

I attempted an answer below but note that your use of TOP 1 without an ordering makes no sense. You need to tell us how you want to order things. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 9, 2018 at 9:49
The order doesn't matter, but I adapted it to work with my table — r3plica
– r3plica, Commented Jan 9, 2018 at 9:54

Tim Biegeleisen · Accepted Answer · 2018-01-09 09:44:45Z

1

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;

answered Jan 9, 2018 at 9:44

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Update query using select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related