How to replicate the unique key (Unique value in a column against a value in another column) against-CodePudding

enter image description here

So I have a table named dragon with 2 columns Pokemon and Power. My result table should look like what is given in the image. How do I do that using SQL? I get a feeling that it is possible using Join. But I am struggling to come up with the right thought process.

CodePudding user response：

if all you want is result as view

SELECT  
  D.Pokemon, 
  CASE WHEN D.Power IS NOT NULL THEN D.Power ELSE D1.power END
FROM  Dragon D
LEFT JOIN 
 ( 
    SELECT 
      Pokemon, 
      MAX(Power) Power 
    FROM Dragon 
    WHERE Power IS NOT NULL 
    GROUP BY Pokemon) D1
ON D.Pokemon = D1.Pokemon

You can also use similar query to update the existing table like below

UPDATE D
SET D.power= D1.power
FROM  Dragon D
    LEFT JOIN 
     ( 
        SELECT 
          Pokemon, 
          MAX(Power) Power 
        FROM Dragon 
        WHERE Power IS NOT NULL 
        GROUP BY Pokemon) D1
    ON D.Pokemon = D1.Pokemon
    WHERE D.Power IS NULL

CodePudding user response：

You can use a window function:

select d.*, max(power) over (partition by pokemon) as imputed_power
from dragon d;

If you want to actually modify the data, then in Standard SQL, you can use a correlated subquery:

update dragon
    set power = (select max(d2.power) from dragon d2 where d2.pokemon = d.pokemon)
    where power is null;