How to update the record's dropdownlist based on precedence?

Question

I have a dropdownlist which contains:

Rank1 
Rank2
Rank3
Rank4

and update button.

I have the following record like

id    name    rank
1     chetan    1
2     ajay      3
3     kelly     2

I want to write update SQL query in such a way that:

when I want to update the rank of chetan(rank1) to rank2 then it should not be updated.
rank3 can be update to rank1 and rank2
rank4 can update to rank1 rank2 and rank3
rank2 should not be update to rank3 and rank4.

It's like top preference. Higher level ranks should not be updated to lower ranks but lower ranks can be updated to higher ranks.

I tried the query using Case when but it's not working:

 update cmp set cmp.rank = case when 'Rank4' Then update cmp set cmp.rank='Rank3'                             or cmp.rank='Rank2' or cmp.rank='Rank1' else cmp.rank end,
                cmp.rank=case when 'Rank3' Then update cmp set cmp.rank='Rank2' or cmp.rank='Rank1' else cmp.rank end,
                ...

Is there any other solution for this?

`rank3 can be update to rank1 and rank2` ... if rank3 can map to more than one value, then how can we possibly give you a query? — Tim Biegeleisen, Feb 08 '19 at 07:04
#chetan kambli----- you should show your expected query result table — Zakir Hossain, Feb 08 '19 at 08:21

score 3 · Accepted Answer · answered Feb 08 '19 at 07:54

3

Here is one way

update t
   set t.rank='<passed_in_value_from_dropdown>'
 where '<passed_in_value_from_dropdown>' < t.rank
   and t.emp = '<passed_in_emp_number>'

answered Feb 08 '19 at 07:54

George Joseph

5,842
10
24

How to update the record's dropdownlist based on precedence?

1 Answers1