MariaDB: Conditional Column Modifications without "Change Column If Exists"

Here's an example:

ALTER TABLE my_table CHANGE COLUMN old_column_name new_column_name new_data_type;

This tries to change the old_column_name to new_column_name with a new data type. If the column doesn't exist, the statement will raise an error.

-- Define the table name and desired changes
SET @table_name = 'my_table';
SET @old_column = 'old_column_name';
SET @new_column = 'new_column_name';
SET @new_type = 'VARCHAR(255)';

-- Attempt to change the column
BEGIN TRY
  ALTER TABLE @table_name CHANGE COLUMN @old_column @new_column @new_type;
  -- Success message (optional)
  SELECT 'Column modified successfully.';
END TRY
BEGIN CATCH -- Handle potential errors
  IF @@ERROR = 1054 -- Column doesn't exist error code
  THEN
    SELECT 'Column does not exist.';
  ELSE
    -- Handle other errors (optional)
    SELECT CONCAT('Error: (', @@ERROR, ') ', @@ERROR_MESSAGE);
  END IF;
END CATCH;

This code defines variables for the table name, old column name, new column name, and data type. It then uses a TRY...CATCH block to attempt the ALTER TABLE statement.

• You can optionally add logic to handle other potential errors within the CATCH block.
• If the error code indicates the column doesn't exist (error code 1054 for MariaDB), a message specifies that.
• If the column exists and the change is successful, a success message is displayed.

Example 2: Checking existence before modification (declarative)

-- Check if the column exists
SELECT COUNT(*) AS column_exists
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'my_table'
  AND COLUMN_NAME = 'old_column_name';

-- Modify the column only if it exists (procedural logic can be implemented here)
IF column_exists > 0 THEN
  ALTER TABLE my_table CHANGE COLUMN old_column_name new_column_name VARCHAR(255);
END IF;

This example first uses a query to check if the old_column_name exists in the my_table using information schema tables.

• This approach allows for more flexibility in handling the existence check. You can integrate this logic into a larger script or procedure.
• If the column_exists count is greater than 0, the column exists, and the ALTER TABLE statement proceeds to modify it.