MySQL convert char column to INT via 2nd table
I have a CHAR type column in table A, and all the strings in the column correspond one-to-one to integ开发者_如何学Pythoners in table B. What I want is to replace all of the strings in the CHAR column of table A with their corresponding INTs from table B. I know I could write a script that makes a new column and populates it with the relevant data, but it seems like there should be an easier way to do it from within MySQL. Is there a way to do this with a UPDATE or ALTER TABLE statement?
Well, simply UPDATING the column with the int value will work:
UPDATE table1
SET table1.column = table2.column
WHERE <condition>
but the column will still be a CHAR column, so afterwards you still have to do an ALTER TABLE to convert the column into an INT.
Though this should not make a problem with casting I would prefer to add a new INT column, fill it with an UPDATE similar to the one above and then remove the old column - always assuming the table is not that big that the repeated ALTER TABLE locks for too long.
精彩评论