SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (id INTEGER, Value INTEGER, Category INTEGER);
INSERT INTO tablename (id, Value, Category) VALUES
  ('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');
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
      );
SELECT * FROM tablename;


id
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