ALTER TABLE WHERE Clause
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)?
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...
上一篇: Sqlite使用子查询更新查询
下一篇: ALTER TABLE WHERE子句