if col1 has the same data, copy col2 data I want to check col1, and if there's 2 of the same in col1, copy the value in col2 that's != 0 to the col2 that is 0. The data comes in from an Excel file that I can't alter. See example below

Input

+----------+-------+
| prime    |   %   |
+----------+-------+
| a1       |   3   |
| a1       |   0   |
| b2       |   25  |
| b3       |   42  |
| c4       |   23  |
| c4       |   0   |
+----------+-------+


Output

+----------+-------+
| prime    |   %   |
+----------+-------+
| a1       |   3   |
| a1       |   3   |
| b2       |   25  |
| b3       |   42  |
| c4       |   23  |
| c4       |   23  |
+----------+-------+
This solution gives you the desired output.  

DECLARE @TempTable TABLE
    (
      Prime CHAR(2) NOT NULL
    , Percentage INT NOT NULL
    );

INSERT  INTO @TempTable
        ( Prime, Percentage )
VALUES  ( 'a1', 3 )
    ,   ( 'a1', 0 )
    ,   ( 'b2', 25 )
    ,   ( 'b3', 42 )
    ,   ( 'c4', 23 )
    ,   ( 'c4', 0 );

WITH CTE_NonZero
    AS ( SELECT   T.Prime
                , MAX(T.Percentage) AS PercentageToUse
        FROM     @TempTable T
        WHERE T.Percentage &lt;&gt; 0
        GROUP BY T.Prime
        )
    UPDATE  @TempTable
    SET     Percentage = P.PercentageToUse
    FROM    @TempTable T
            INNER JOIN CTE_NonZero P ON P.Prime = T.Prime
    WHERE   T.Percentage = 0;

SELECT * FROM    @TempTable; 


Alternatively, you could make the CTE updatable and thus avoid the join:

WITH MaxPercentages
    AS ( SELECT   T.Percentage
                , MAX(T.Percentage) OVER (PARTITION BY T.Prime) AS PercentageToUse
        FROM     @TempTable T
        )
    UPDATE  MaxPercentages
    SET     Percentage = PercentageToUse
    WHERE   Percentage = 0 AND PercentageToUse &lt;&gt; 0;