Sql server map action to state to get the right result-CodePudding

For a database I need to log a state from the action of a different table.

Table 1              
| ID  | Action   |   
| 1   | Accept   |   
| 2   | Save     |   
| 3   | Withdraw |  
| 4   | Accept   | 

Table 2           
| ID  | State|
| 1   |      |
| 2   |      |
| 3   |      |
| 4   |      |
Result
Table 2           
| ID  | State     |
| 1   | Accepted  |
| 2   | Draft     |
| 3   | Withdrawn |
| 4   | Accepted  |

I was thinking of using a CASE WHEN query to solved this like:

Update table 2
set state = action
from (select 
       case when action == Accept then Accepted
            when action == save then draft
            when action == withdraw then withdrawn
        end
      from table 1
)

I was wondering if this is the best way or if its possible to use some sort of mapper for this.

CodePudding user response：

Ideally you should maintain some kind of junction table which maps actions in Table1 to states in Table2. Consider:

Table12
ID | State
1  | Accepted
2  | Draft
3  | Withdrawn

UPDATE t2
SET State = t12.State
FROM Table2 t2
INNER JOIN Table12 t12
    ON t12.ID = t2.ID;

CodePudding user response：

you could make a stored procedure that implements the mapping, then call it to update. that way if you need to change th map, you just modify the stored procedure