In a local SQLite (vs 3.29.0) database, there is a table with 3 columns (excluding the rowID). Two contain values, and one contains categories. I want to update the category based on a range around the values of one specific category. It needs to be possible that the category that is SET is the same category as the one that determines the range.

Example:

I want rows to be changed to category 2, based on a range of 4 around value and a range of 2 around value2. This should change only rows 6, 9 and 11:

My current SQL statement is:

UPDATE tablename
SET Category = 2
WHERE (Category != 2
  AND EXISTS (
        SELECT *
        FROM tablename t
        WHERE t.Category = 2
          AND tablename.Value BETWEEN t.Value - 4 AND t.Value   4
          AND tablename.Value2 BETWEEN t.Value2 -2 AND t.Value2  2)
          );

of which the result is:

What appears to be happening is that due to row 6 changing to category 2, row 13 is now within range of the values of a row that is in category 2, and therefore is also assigned category 2. How do I change the statement so that the SET is only applied to the values that were within range initially?

See the demo for the example.

CodePudding user response：

If your version of SQLite is 3.33.0 you can use the join-like UPDATE...FROM syntax to perform a self join in the UPDATE statement:

UPDATE tablename AS t1
SET Category = t2.Category
FROM tablename AS t2 
WHERE t2.Category = 2
  AND t1.Category <> t2.Category
  AND t1.Value BETWEEN t2.Value - 4 AND t2.Value   4
  AND t1.Value2 BETWEEN t2.Value2 - 2 AND t2.Value2   2;

For previous versions of SQLite, first create a temporary table with all the rows of the table with Category = 2:

CREATE TEMPORARY TABLE t AS 
SELECT * FROM tablename WHERE Category = 2;

and then update the table:

UPDATE tablename
SET Category = 2
WHERE Category <> 2
  AND EXISTS (
        SELECT 1
        FROM t
        WHERE tablename.Value BETWEEN t.Value - 4 AND t.Value   4
          AND tablename.Value2 BETWEEN t.Value2 -2 AND t.Value2   2
      );

See the demo.