0

Here is my situation, I have a table called Statuses (statusID, statusName) with 22 statuses, and there are other tables that have statusID columns.

Now the customer wants to consolidate all the 22 statuses in the Statuses table into 13 statuses. Then we have to update, exactly speaking, map all statusID in all other tables.

Can anyone help me out here?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
GLP
  • 3,441
  • 20
  • 59
  • 91

1 Answers1

5

Since this sounds like a one time thing the easiest way is to hard code the map.

e.g. 

UPDATE 
  TABLE
SET StatusID = CASE WHEN StatusID = 1 THEN 5
                    WHEN StatusID = 2 THEN 5
                    WHEN StatusID = 3 THEN 1
                    WHEN StatusID = 4 THEN 5
                    WHEN StatusID = 5 THEN 2
                    ...17 more times
               END

or if you already have a mapping table

UPDATE 
  TABLE
SET StatusID = map.NewStatusID
FROM
       TABLE as T
       INNER JOIN Map
       ON t.StatusID = map.OldStatusID
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Thanks. Is it possible to use select query for the statusId? I have a mapping table StatusesMapping (OldStatusID, NewStatusID). – GLP Feb 27 '12 at 16:59
  • Thanks. That is exactly what I want. If the customer want to change the table later, all I need is to change the mapping table, not the script. – GLP Feb 27 '12 at 17:07
  • Out of interest why do you use an `INNER JOIN` for your inner query rather than a `SELECT...WHERE StatusID = map.OldStatusID`? – jwg Oct 11 '13 at 14:58
  • @jwg In this example it doesn't matter. But there are plenty of cases where does so by default I always use JOIN syntax. You can look at [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/q/1018822) for more discussion on this – Conrad Frix Oct 11 '13 at 16:25
  • @ConradFrix thanks. I have come across the advantages of `INNER JOIN` in general queries, and I agree it is nice to use that syntax everywhere (once one is used to it). – jwg Oct 14 '13 at 09:02