How to change the value of a field in a SQL query, depending on the value in another field?

Question

SQL

Hello, help me make a request, there is a table:
obj_id | field_id | rel_val | float_val
22 | 208 | NULL | 1999
22 | 402 | 1 | NULL
......
25 | 208 | NULL | 2100
25 | 402 | 2 | NULL
...
29 | 208 | NULL | 7100
29 | 402 | 3 | NULL
The query in "words" sounds like this:
if field_id = 208 and in this line float_val is greater than 1000 and less than 2000,
then in the line with the same obj_id, find field_id = 402 and update rev_val by 1
If
if field_id = 208 and in this then the float_val line is greater than 2000 and less than 3000,
then in the line with the same obj_id, find field_id = 402 and update rev_val to 2
Thanks

Answer 1

This is the solution that saved me.

UPDATE table AS t1
JOIN (SELECT obj_id, MIN(float_val) AS 'f'
      FROM table
      WHERE field_id in (208, 209, 210)
      GROUP BY obj_id) t2
ON cms3_object_content_copy.obj_id = t2.obj_id
SET rel_val = CASE WHEN f BETWEEN 0 AND 999 THEN 1
                   WHEN f BETWEEN 1000 AND 2000 THEN 2
                   ELSE 3
              END
WHERE table.field_id = 402

Answer 2

I haven't tested it, but IMHO it should work somehow (without using T-SQL or other extensions).
Wrote for the first case, for the second similarly

UPDATE t as t1 
SET rev_val=1
WHERE t1.field_id=402 AND t1.obj_id IN (
    SELECT t2.obj_id FROM t as t2 
          WHERE t2.field_id=208 AND t2.float_val>1000 AND t2.float_val<2000
);