Change the order of a table according to preference in another table

In MS SQL server, I have a table A as below

Name   Food    PreferenceOrder
Jon    Burger  1
Jon    Coke    2
Jon    Pizza   3
Jon    Fries   4
Sam    Pizza   1
Sam    Coke    2

I have another table B that can override the preference order above

Name   Food    PreferredOverFood
Jon    Pizza   Fries
Jon    Coke    Burger
Jon    Fries   Coke
Sam    Coke    Pizza

Basically here, Food should come before PreferredOverFood (Pizza > Fries)

Now, I want to reorder table A according to Table B preferences, so the result should be like

Name   Food    PreferenceOrder
Jon    Burger  4
Jon    Pizza   1
Jon    Fries   3
Jon    Coke    2
Sam    Pizza   2
Sam    Coke    1

I tried by using cursors, so I created a dynamic cursor, with each fetch I am updating Table B with table A preference, but since we are updating things row by row its not considering rows that violate the previous preferences, so I am getting Fries before Pizza (since Fries > Coke is run and it forgot about first preference (Pizza > Fries)).

So dynamic cursor is not working, (its not refreshing the result set after update). Can I use CTE or something to do like above. (Can also have circular dependencies, but not too worried about it for now)

Thanks

It is too long for a comment.

Well, that’s the problem, then. I take data from A, then apply overrides from B to it and end up with a cycle. I think that there is a cycle, you don’t think that there should be a cycle.

So, there is a bigger problem with understanding how overrides should work.

My logic is the following.

Data from A can be represented in the same format as B:

Name   Food    PreferenceOrder
Jon    Burger  1
Jon    Coke    2
Jon    Pizza   3
Jon    Fries   4

So, for Jon we have this chain originally:

Burger -> Coke -> Pizza -> Fries

OR this set of rules:

a) Burger -> Coke
b) Coke -> Pizza
c) Pizza -> Fries

Then we have overrides from B:

Name   Food    PreferredOverFood
Jon    Pizza   Fries
Jon    Coke    Burger
Jon    Fries   Coke

OR this set of rules:

d) Pizza -> Fries
e) Coke -> Burger
f) Fries -> Coke

Now we merge two sets of rules where second set overrides the first set if there is a conflict.

Rule (d) is the same as (c), so (c) is removed.
Rule (e) overrides (a), so (a) is removed.
Rule (f) is a new rule that doesn’t exist in the first set.
If any rules from the first set are still there, they are kept.

Final result of rules:

d) Pizza -> Fries
e) Coke -> Burger
f) Fries -> Coke
b) Coke -> Pizza

This is a directional graph. If it represents a tree, it can be ordered. It there are cycles, you need to decide what to do with them. If there are several disconnected trees, you can order each tree individually and then decide the order between trees.

In your example the graph looks like this (if we start from Coke).

    (b)      (d)      (f)
Coke -> Pizza -> Fries -> Coke -> ... the cycle (bdf) ...
   |
   | (e)
   |
   -> Burger