ALTER COLUMN

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;

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;

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;

-- 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.

SQL ALTER COLUMN Labs