What would the syntax be to write an update statement to update the null values for fields power1, power2, power3
with the values already listed in the table?
I take this to mean that each company's nulls should be populated based on the values taken from the one populated entry that, as you say elsewhere in your post, each company has. So, in pseudo-code, your UPDATE statement would need to look something like this:
UPDATE
#Rentarious
SET
power1 = power1 from the same company’s populated row,
power2 = power2 from the same company’s populated row,
power3 = power3 from the same company’s populated row
WHERE power1 IS NULL
AND power2 IS NULL
AND power3 IS NULL
;
One way to implement the above pattern could be to use correlated subqueries:
UPDATE
#Rentarious
SET
power1 = (SELECT power1 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power1 IS NOT NULL),
power2 = (SELECT power2 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power2 IS NOT NULL),
power3 = (SELECT power3 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power3 IS NOT NULL)
WHERE power1 IS NULL
AND power2 IS NULL
AND power3 IS NULL
;
While that would work, such an update might not be very efficient, because the same table would be touched three extra times to obtain the source value. Since you know that the non-null values are stored in one row for each company, you could obtain them all in just one extra pass over the table by using a derived table and the proprietary "update with a join" syntax:
UPDATE
#Rentarious
SET
power1 = sub.power1,
power2 = sub.power2,
power3 = sub.power3
FROM
(
SELECT
c1,
power1,
power2,
power3
FROM
#Rentarious
WHERE power1 IS NOT NULL
AND power2 IS NOT NULL
AND power3 IS NOT NULL
) AS sub
WHERE #Rentarious.c1 = sub.c1
AND #Rentarious.power1 IS NULL
AND #Rentarious.power2 IS NULL
AND #Rentarious.power3 IS NULL
;
You can also rewrite it to use the explicit JOIN syntax:
UPDATE
tgt
SET
power1 = src.power1,
power2 = src.power2,
power3 = src.power3
FROM
#Rentarious AS tgt
INNER JOIN
(
SELECT
c1,
power1,
power2,
power3
FROM
#Rentarious
WHERE power1 IS NOT NULL
AND power2 IS NOT NULL
AND power3 IS NOT NULL
) AS sub
ON tgt.c1 = sub.c1
WHERE tgt.power1 IS NULL
AND tgt.power2 IS NULL
AND tgt.power3 IS NULL
;
As you can see, there is only one subquery in both variations and it provides all three values to populate the other rows.
Note, though, that it is also possible to solve this problem without any extra scans. First of all, if this were a SELECT statement, you could return the populated row's values in every row by using a window aggregate function, like this:
SELECT
c1,
yryryryr,
power1,
power2,
power3,
populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
FROM
#Rentarious
;
The MAX function works in this situation because it returns the maximum value only across the non-null values in the specified set. In your case, there would be only one non-null value per partition of c1
in each of the three cases, so the function would return that one value. This would be the result of the query for the example in your question:
c1 yryryryr power1 power2 power3 populatedPower1 populatedPower2 populatedPower3
--------------- ---------- ------ ------ ------ --------------- --------------- ---------------
Building Blocks 2012-01-01 NULL NULL NULL Red Blue Green
Building Blocks 2013-01-01 NULL NULL NULL Red Blue Green
Building Blocks 2014-01-01 NULL NULL NULL Red Blue Green
Building Blocks 2016-01-01 Red Blue Green Red Blue Green
Red Cement 2012-01-01 Pink Purple Orange Pink Purple Orange
Red Cement 2016-01-01 NULL NULL NULL Pink Purple Orange
Red Cement 2011-01-01 NULL NULL NULL Pink Purple Orange
So the only thing remaining would be just to
SET
power1 = populatedPower1,
power2 = populatedPower2,
power3 = populatedPower3
And it is indeed possible to do so in SQL Server, because the result of the above SELECT query can be used as the target of your UPDATE statement. You can use it as a derived table:
UPDATE
tgt
SET
power1 = populatedPower1,
power2 = populatedPower2,
power3 = populatedPower3
FROM
(
SELECT
c1,
yryryryr,
power1,
power2,
power3,
populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
FROM
#Rentarious
) AS tgt
WHERE power1 IS NULL
AND power2 IS NULL
AND power3 IS NULL
;
or implement it as a CTE (Common Table Expression) and use the CTE's alias as the target:
WITH tgt AS
(
SELECT
c1,
yryryryr,
power1,
power2,
power3,
populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
FROM
#Rentarious
)
UPDATE
tgt
SET
power1 = populatedPower1,
power2 = populatedPower2,
power3 = populatedPower3
WHERE power1 IS NULL
AND power2 IS NULL
AND power3 IS NULL
;
Both would work equally well and result in all nulls replaced with corresponding values, i.e. from this:
c1 yryryryr power1 power2 power3
--------------- ---------- ------ ------ ------
Building Blocks 2016-01-01 Red Blue Green
Red Cement 2012-01-01 Pink Purple Orange
Building Blocks 2012-01-01 NULL NULL NULL
Building Blocks 2013-01-01 NULL NULL NULL
Building Blocks 2014-01-01 NULL NULL NULL
Red Cement 2016-01-01 NULL NULL NULL
Red Cement 2011-01-01 NULL NULL NULL
to this:
c1 yryryryr power1 power2 power3
--------------- ---------- ------ ------ ------
Building Blocks 2016-01-01 Red Blue Green
Red Cement 2012-01-01 Pink Purple Orange
Building Blocks 2012-01-01 Red Blue Green
Building Blocks 2013-01-01 Red Blue Green
Building Blocks 2014-01-01 Red Blue Green
Red Cement 2016-01-01 Pink Purple Orange
Red Cement 2011-01-01 Pink Purple Orange