CODEWITHSUNDEEP
sqlsql-servert-sql
andruidthedude
andruidthedude

Reputation: 275

Update multiple rows based on unique values in another column in same table

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

Answers (1)

MatBailie
MatBailie

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

Related Questions