check for same range in postgresql

I wanted to check if for one particular student my column (Amount) contains a value less than 10, and if yes, then check if for the same student my column (Amount) is also greater than 10 and update the column new accordingly in PostgreSQL.

MY table:

Tried this way but not working

update table t1 set "New"='pass'
where cast("Amount" as numeric) <= 10 and cast("Amount" as numeric) > 10 

The output I’m expecting:

Answer

One option is to self-join the table with an aggregate subquery, and then do a conditional assignment in the outer query:

update t1
set new = case when min_amount <= 10 and max_amount > 10 then 'pass' else 'not pass' end
from (
    select name, min(amount::numeric) min_amount, max(amount::numeric) max_amount
    from t1 
    group by name 
) t2
where t1.name = t2.name

Demo on DB Fiddle:

name  | amount | new     
:---- | :----- | :-------
Harry | 3      | pass    
Harry | 50     | pass    
Ron   | 5      | not pass