ALTER TABLE WHERE Clause

Question

I am not well versed with DDL. I was under the assumption that they was a WHERE Clause for ALTER and I have understood now after some research that WHERE Clause doesn't exist for ALTER Command. How to handle cases where we might need to check some conditions in the ALTER Command?

Mysql Drop column where all row value is null

For example, for the above question, If I want to write something like the code below How do I do it?

ALTER TABLE my_table DROP col
WHERE NOT EXISTS (SELECT * FROM my_table
                    WHERE col IS NOT NULL)

Is there any standard SQL way to achieve this? If not can anyone provide ways to do it for the various databases (SQL Server, MySQL, Oracle, PostgreSQL)?

Answer 1

You cannot drop a column for only certain rows in a table. The table either has the column or it doesn't. This is why there is no WHERE clause for ALTER.

It sounds like you are looking to drop this column only if all of the values are NULL in the table. What you need is an IF statement.

Updated:

SELECT CASE 
    WHEN NOT EXISTS (SELECT * FROM my_table WHERE col IS NOT NULL)
        THEN 'Y'
    ELSE 'N'
END AS my_result

IF my_result = 'Y'
    ALTER TABLE...