SQL ALTER COLUMN Keyword
ALTER COLUMN
The ALTER COLUMN
command is used to change the data type of a column in a table.
Note: SQLite (used in our playground) does not support the
ALTER COLUMN
syntax directly. To change a column’s data type, you must recreate the table.
SQL ALTER COLUMN Syntax (Not supported in SQLite)
ALTER TABLE table_name ALTER COLUMN column_name datatype;
SQL ALTER Example (Not supported in our playground)
The following SQL changes the data type of the column named BirthDate
in the Employees
table to type YEAR
:
ALTER TABLE Employees ALTER COLUMN BirthDate YEAR;
Alternative Method
To change a column’s data type in SQLite, use the following approach:
-- Step 1: Create new table with modified column type CREATE TABLE Patients_new ( patient_id INTEGER PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), dob TEXT, -- Changed data type from DATE to TEXT (example) gender CHAR(1), contact_number VARCHAR(15) ); -- Step 2: Copy data from old table INSERT INTO Patients_new SELECT * FROM Patients; -- Step 3: Drop old table DROP TABLE Patients; -- Step 4: Rename new table ALTER TABLE Patients_new RENAME TO Patients;
This method allows you to effectively alter a column’s data type in our playground environment using SQLite-compatible SQL.