Sql-server – T-SQL (SQL Server 2012) Update based on lookup table

sql serversql-server-2012t-sql

I have the following tables related to this question:

CREATE TABLE tblTitan(
titanId int IDENTITY (15483,1) PRIMARY KEY,
userId int UNIQUE NOT NULL,
titanName varchar(15) NOT NULL,
titanExperience int DEFAULT 0,
titanLevel int DEFAULT 1,
titanStep int DEFAULT 1,
titanBattleCount int DEFAULT 0,
titanWinCount int DEFAULT 0,
titanLossCount int DEFAULT 0,
titanDateCreated date DEFAULT GetDate(),
titanDateLegend date DEFAULT NULL,
titanNpcFlag bit DEFAULT 0,
titanActiveFlag bit DEFAULT 1,
titanImage varchar(150) DEFAULT 'basicTitan.png',
titanElementType int NOT NULL UNIQUE,
FOREIGN KEY (userId) REFERENCES tblUser (userId),
FOREIGN KEY (titanElementType) REFERENCES tblElement (elementId),
);

CREATE TABLE tblLevel(
levelId int IDENTITY (1,1) PRIMARY KEY,
levelNo int NOT NULL,
levelStep int NOT NULL,
levelExperienceMin int NOT NULL,
levelExperienceMax int NOT NULL,
);

Scenario

A titan has battles with other titans, during which an algorithm calculates a winner, and xp is awarded to the titan. After each battle, I want to check if a titan's experience crosses a threshold from the tblLevel table, which is a lookup table.

tblLevel has values such as the following:

  • levelId 1
    • levelNo 1
    • levelStep 1
    • levelExperienceMin 0
    • levelExperienceMax 100
  • levelId 2
    • levelNo 1
    • levelStep 2
    • levelExperienceMin 101
    • levelExperienceMax 200
  • levelId 3
    • levelNo 2
    • levelStep 1
    • levelExperienceMin 201
    • levelExperienceMax 300

Question

Using t-sql code, how may I update the tblTitan level and step values based upon the experience the titan has? eg: if a titan has 110 experience, I want to update the titanLevel and titanStep values from (1, 1) to (1, 2), as dictated by the tblLevel lookup table.

I have tried fiddling around with the update function, but I am not too sure how to use a subquery or other method to be able to check if the experience is between the min and max ranges.

An example attempt of my code is:

UPDATE tblTitan t, tblLevel l
AS
SET t.titanStep = l.levelStep
WHERE t.titanExperience = >= l.levelExperienceMin AND <= l.levelExperienceMax

This sort of command SEEMS to make sense in my head, but is completely invalid in T-SQL. If somebody could please help me with the code or point me in the right direction that would be greatly appreciated!

Best Answer

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 < and > operators like a BETWEEN operator: A > B AND < 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 >= l.levelExperienceMin 
    AND t.titanExperience <= 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 < 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 >= l.levelExperienceMin 
    AND t.titanExperience <= 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
Related Question