For simplicity, let's assume you have a table that looks something like this:
# Our original table
CREATE TABLE my_table (
id INT NOT NULL AUTO_INCREMENT,
my_col TEXT NOT NULL,
PRIMARY KEY (id)
);
What you want to do is convert the my_col
column into a VARCHAR column. The following steps should get you from TEXT to VARCHAR:
# Create a temp table
CREATE TABLE tmpvchar (
id INTEGER,
my_col_vchar VARCHAR(255)
);
# Copy the data over and cast it to a character
INSERT INTO tmpvchar (id, my_col_vchar)
SELECT id, CAST(my_col AS CHAR(255)) FROM my_table;
# Add a new column to the original table
ALTER TABLE my_table ADD my_col_vchar VARCHAR(255);
# Copy the data from the temp table
UPDATE my_table SET my_col_vchar = (SELECT my_col_vchar FROM tmpvchar WHERE my_table.id = tmpvchar.id);
# Remove the incorrect column
ALTER TABLE my_table DROP my_col;
# Rename the column back to the correct one
ALTER TABLE my_table CHANGE my_col_vchar my_col VARCHAR (255) NOT NULL;
# Drop the temporary table
DROP TABLE tmpvchar;
I hope this ends up saving someone some time!
Found this useful! You can actually save yourself a few lines if you drop the column before creating the new one.
ReplyDeleteSomething like this
CREATE TABLE backup AS (SELECT * FROM original):
ALTER TABLE original DROP textfield, ADD textfield VARCHAR(32);
UPDATE original SET textfield = (SELECT * FROM backup WHERE backup.id = original.id)
DROP TABLE backup
Persistent state refers to passive options on some slot machines, a few of which capable of to} set off bonus payouts or different special options if certain situations are met over time by players on that machine. A slot machine's standard layout features a display screen displaying three 카지노 사이트 or extra reels that "spin" when the game is activated. Some trendy slot machines still embody a lever as a skeuomorphic design trait to set off play. However, the mechanics of early machines have been superseded by random quantity mills, and most are now are|are actually} operated utilizing buttons and touchscreens. With virtually seven-hundred ready-to-pay slots, The Mill Casino welcomes you to try your hand at a game of likelihood.
ReplyDelete