开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜