CODEWITHSUNDEEP
sqldatabasegetsetwhere-clause
2b77bee6-5445-4c77-b1eb-4df3e5
2b77bee6-5445-4c77-b1eb-4df3e5

Reputation: 967

SQL: Variable to update a set of items

SO, each item has an id, a setId, and a name.

Current data may only have one NAME field filled out in an entire set.

EX>

>ID = 1 >NAME = 'Bob' >SETID = 5
>ID = 2 >NAME = NULL  >SETID = 5
>ID = 3 >NAME = NULL  >SETID = 5
>ID = 4 >NAME = NULL  >SETID = 5

I am looking for an SQL script that GET the one record that is not null in a "SET" SET the remaining items in the "SET" for that "NAME" record.

The end result I am aiming for would look like this:

>ID = 1 >NAME = 'Bob' >SETID = 5
>ID = 2 >NAME = 'Bob' >SETID = 5
>ID = 3 >NAME = 'Bob' >SETID = 5
>ID = 4 >NAME = 'Bob' >SETID = 5

Upvotes: 1

Views: 47

Answers (1)

Dave Zych
Dave Zych

Reputation: 21887

Assuming there is only one Name in a set that is not null, you could use a CTE and do something like:

;WITH NameHelper AS 
(
    SELECT
        Name,
        SetID
    FROM MyTable
    WHERE Name IS NOT NULL
    GROUP BY Name, SetID
)

UPDATE MyTable SET Name = NameHelper.Name
FROM MyTable
    INNER JOIN NameHelper ON MyTable.SetID = NameHelper.SetID

Upvotes: 1

Related Questions