SQL Server 2014 – How to Run a Proc Against All Records in a Temp Table

From your examples, the statement below should do what you want to do. It's not fast as it involves casting ID to a string and manipulating it, but I think this is the only thing that will work for you.

UPDATE TEST 
SET ID = CAST(LEFT(CAST(ID AS VARCHAR(20)), LEN(CAST(ID AS VARCHAR(20)) - 1)) + '1' + RIGHT(CAST(ID AS VARCHAR(20)), 1) AS INT)

If ID is a string to begin with then you can remove the CAST(ID AS VARCHAR(20)) statement and replace it with just ID.

UPDATE TEST 
SET ID = CAST(LEFT(ID, LEN(ID) - 1)) + '1' + RIGHT(ID, 1) AS INT)

This seemed like a relational division problem to me:

DECLARE @results TABLE ( ID INT NOT NULL, PASSED BIT NOT NULL, LEVEL INT NOT NULL, CREATEDATE DATE NOT NULL, PRIMARY KEY ( ID, LEVEL, PASSED, CREATEDATE ) )
DECLARE @levels TABLE ( LEVEL INT PRIMARY KEY )

INSERT INTO @levels
VALUES ( 1 ), ( 2 ) --, ( 3 )    
INSERT INTO @results
VALUES
    ( 100, 1, 2, '1 Oct 2016' ),
    ( 100, 0, 2, '1 Sep 2016' ),
    ( 100, 1, 2, '1 Aug 2016' ),
    ( 100, 1, 1, '1 Jul 2016' ),
    ( 101, 1, 1, '1 Oct 2016' ),
    ( 102, 1, 1, '1 Oct 2016' ),
    ( 102, 1, 1, '1 Sep 2016' ),
    ( 103, 0, 1, '1 Oct 2016' ),
    ( 103, 1, 2, '1 Sep 2016' )
    ,
    ( 200, 0, 1, '1 Sep 2016' ),        -- No pass at all
    ( 201, 1, 1, '1 Sep 2016' ),        -- 1 pass at level 1 only
    ( 202, 1, 1, '1 Sep 2016' ),        -- 1 pass at each level 
    ( 202, 1, 2, '1 Sep 2016' ),        -- 1 pass at each level 
    ( 203, 1, 2, '1 Sep 2016' ),        -- 1 pass at level 2 only 
    ( 204, 0, 1, '1 Sep 2016' ),        -- No pass at either level
    ( 204, 0, 2, '1 Sep 2016' )         -- No pass at either level

    SELECT ID, 
        CASE 
            WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
              AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
            THEN 1
            ELSE 0 
        END overallPass
    FROM @results
    GROUP BY ID

I have not included the CREATEDATE column in the calculation as it doesn't seem to add a lot. Also, does it really make sense for someone to pass a course at the same level twice (as per ID 100 in your sample data)? Does the order in which they pass the courses really matter? If so, then this could cause a bug in my code where Sum of PASSED >= COUNT ( DISTINCT LEVEL ). Pre-aggregating the data would solve this, eg

SELECT ID,
    CASE 
        WHEN SUM( CAST( PASSED AS INT ) ) >= COUNT( DISTINCT LEVEL )
          AND COUNT( DISTINCT LEVEL ) = ( SELECT COUNT( DISTINCT LEVEL ) FROM @levels)
        THEN 1
        ELSE 0 
    END overallPass
FROM ( SELECT ID, LEVEL, MAX( CAST( PASSED AS INT ) ) PASSED FROM @results GROUP BY ID, LEVEL ) x
GROUP BY ID

If the date really is important, please post back and we'll need a slightly more complicated query, basically the PASSED status associated with the max date per ID.

HTH