T-SQL (SQL Server 2012) Update based on lookup table
My first question is: 


  How would you write a query (SELECT) with a similar pattern?


I can see several problems with the UPDATE statement:


UPDATE on 2 tables (tblTitan t, tblLevel l)
No FROM clause
Old JOIN syntax (TableA, TableB VS FROM TableA INNER JOIN TableB ON ...)
Invalid (missing) expression in the WHERE clause (usage of &lt; and > operators like a BETWEEN operator: A > B AND &lt; C)


A SELECT statement would be like this:

SELECT titanId, titanName
    , t.titanLevel, t.titanStep
    , l.levelNo , l.levelStep
FROM @tblTitan t
INNER JOIN @tblLevel l
    ON t.titanExperience &gt;= l.levelExperienceMin 
    AND t.titanExperience &lt;= l.levelExperienceMax ;



tblTitan t, tblLevel l is replaced by a proper INNER JOIN clause
the WHERE clause is replaced by the ON clause in the INNER JOIN
the invalid BETWEEN like WHERE clause is replace by 2 expresions with &lt; and >


From this valid query, the FROM ... JOIN ... WHERE part can be kept and the SELECT can be replaced by an UPDATE t and a SET clauses.

The UPDATE statement can be writen like this:

UPDATE t
    SET t.titanLevel = l.levelNo
        , t.titanStep = l.levelStep
FROM @tblTitan t
INNER JOIN @tblLevel l
    ON t.titanExperience &gt;= l.levelExperienceMin 
    AND t.titanExperience &lt;= l.levelExperienceMax 
-- WHERE titanId = ...;




Sample data with 3 levels and 3 titans:

DECLARE @tblTitan TABLE (titanId int, titanName varchar(15) , titanExperience int, titanLevel int, titanStep int)
INSERT INTO @tblTitan(titanId, titanName, titanExperience, titanLevel, titanStep) VALUES
    (1, 'Bob', 110, 55, 66)
    , (2, 'Sam', 220, 77, 88)
    , (3, 'Isa', 50, 22, 33)

DECLARE @tblLevel TABLE(levelId int, levelNo int, levelStep int, levelExperienceMin int, levelExperienceMax int)
INSERT INTO @tblLevel(levelId, levelNo, levelStep, levelExperienceMin, levelExperienceMax) VALUES
    (1, 1, 1, 0, 100)
    , (2, 1, 2, 101, 200)
    , (3, 2, 1, 201, 300)


Output of SELECT before UPDATE:

titanId | titanName | titanLevel    | titanStep | levelNo   | levelStep
3       | Isa       | 22            | 33        | 1         | 1
1       | Bob       | 55            | 66        | 1         | 2
2       | Sam       | 77            | 88        | 2         | 1


Output of SELECT after UPDATE:

titanId | titanName | titanLevel    | titanStep | levelNo   | levelStep
3       | Isa       | 1             | 1         | 1         | 1
1       | Bob       | 1             | 2         | 1         | 2
2       | Sam       | 2             | 1         | 2         | 1