Reputation: 275
I have a table with two columns. The table columns are name, and companyID, and they are in the [dbo].[Suppliers] table.
I need to update the CompanyID values ONLY for Unique Names.
UPDATE [dbo].[Suppliers]
SET CompanyId = 46
WHERE Name IN
(
SELECT DISTINCT Name
FROM [dbo].[Suppliers]
);
i.e.
Trying to get this
Name CompanyID
A 5
B 5
C 5
A 5
To look like:
Name CompanyID
A 6
B 6
C 6
A 5
Unfortunately, my query above is not doing the trick.
Appreciate any and all help. Thanks.
Upvotes: 0
Views: 963
Reputation: 86808
You can use a Common Table Expression to add a row number to each name, then update that CTE but specify only the first row for each name...
WITH
uniquely_identified AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY name ORDER BY companyID) AS name_row_id,
*
FROM
[dbo].[Suppliers]
)
UPDATE
uniquely_identified
SET
CompanyId = 46
WHERE
name_row_id = 1
;
Example: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4b5eba30b3bed71216ec678e9cffa6b9
Upvotes: 3