0

I have a table I imported from excel. Everything was imported as strings to get started and gradually the data types were fixed.

I am now going through the process of normalisation and to start I done a select distinct on a given row to extract out the columns that were repeated to another table with an id.

Now I am trying to replace every occurrence of the string in the original table, with the id of the corresponding string in the other table.

I can do it one by one with something like this no prob...

UPDATE myTable
SET myCol = REPLACE(myCol, 'String', 'Num')

and later convert the myCol to an int.

But having to run this, and various variations on it for every string in the system is very error prone.

For example, if I accidently use the same number twice, replacing every string...I cant tell anymore which recordset originally belonged to the first update, and which were the new ones. This is a problem when the dataset is as large as mine.

Is there some way I can combine this with a join on the tables and have the system automate the process for me. I can imagine with normalisation being such a big thing for the last 30 or more years, this issue must have been met and resolved by now.

Any help would be appreciated.

Francis Rodgers
  • 4,565
  • 8
  • 46
  • 65
  • 1
    add a new column and do a join and update the id and later delete the string value – radar Oct 15 '14 at 16:20
  • 1
    You can use UPDATE with JOIN see this [link](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match/224740#224740) or you can use SSIS package – Max Oct 15 '14 at 16:55
  • I used a combo of both these comments, so whoever puts it as an answer first will get accepted. Thank for your help. – Francis Rodgers Oct 16 '14 at 08:26

0 Answers0