Select values that are within a certain range but exclude the values that determine the range in Sql-CodePudding

In a SQLite local database, I have a 2 column-table, one containing values, and one containing categories. I want to update the categories of certain rows based on the following selection:

select the rows that are in a certain category
determine the values for those rows.
select the rows that have values within a certain range of the values of the already selected rows.
update the rows that are within the second selection, but exclude those that are in the first selection.

The statement that I have now (that does not work) is as follows:

UPDATE table SET category = '3' WHERE 
(
value BETWEEN 
(
((SELECT value FROM table WHERE category = '2')  4) 
AND 
((SELECT value FROM table WHERE category = '2') -4)
EXCEPT SELECT value FROM table WHERE category = '2'
)
... (further constraints)
)

This runs without error, but does not actually appear to select anything, as nothing is updated. What is the correct way to get this update to work?

EDIT: as requested an example with tables:

rowid	Value	Category
1	20	2
2	30	2
3	40	2
4	70	2
5	5	1
6	19	1
7	26	1
8	42	1
9	49	1
10	52	1
11	71	1
12	90	1

I want the values of the rows that are currently in category 1, to be placed in category 3, based on a range of 4 around the values of the rows that are in category 2. So in this case any row that has category = 1, that has a value of either 16-24, 26-34, 36-44 or 66-74.

rowid	Value	Category
1	20	2
2	30	2
3	40	2
4	70	2
5	5	1
6	19	3
7	26	3
8	42	3
9	49	1
10	52	1
11	71	3
12	90	1

CodePudding user response：

You can use EXISTS:

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

See the demo.