SQL - Update table column values using expression based on two tables

Question

Using a Sqlite database, I have a users table and a ranks table with the following columns:

users table: id | rankid | xp

ranks table id | name | xpLowerLevel

My goal is update the rankId field for all user rows based on the xpLowerLevel field from the ranks table. My Sql expression as follows:

UPDATE users 
SET rankId = (SELECT id FROM ranks 
              WHERE xpLowerLevel <= users.xp  
              ORDER BY ABS(xpLowerLevel - users.xp) 
              LIMIT 1);

Which gives me the following error no such column: users.xp. What am I doing wrong ?

Answer 1

With FIRST_VALUE() window function:

UPDATE users 
SET rankId = (
  SELECT DISTINCT FIRST_VALUE(id) OVER (ORDER BY ABS(xpLowerLevel - users.xp))
  FROM ranks 
  WHERE xpLowerLevel <= users.xp  
);

Or since there is already the condition:

WHERE xpLowerLevel <= users.xp

the difference:

users.xp - xpLowerLevel

is >= 0,
so there is no need for the function ABS():

UPDATE users 
SET rankId = (
  SELECT DISTINCT FIRST_VALUE(id) OVER (ORDER BY users.xp - xpLowerLevel)
  FROM ranks 
  WHERE xpLowerLevel <= users.xp  
);