3

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)?

Community
  • 1
  • 1
Ram
  • 3,092
  • 10
  • 40
  • 56
  • It needs to be done using if condition using t-SQL in case of SQL server – radar Oct 13 '14 at 03:03
  • What type of database do you want to run this against? They will all have different syntaxes – Nick.Mc Oct 13 '14 at 03:27
  • It would be great if you can provide a answer including the various syntaxes like [this](http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql/1293347#1293347) answer – Ram Oct 13 '14 at 03:43

1 Answers1

5

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...
dursk
  • 4,435
  • 2
  • 19
  • 30
  • I understand why there is no `WHERE` clause for `ALTER` and that was not my question. – Ram Oct 13 '14 at 03:09
  • Can you add an example using the `IF` statement as mentioned? – Ram Oct 13 '14 at 03:13
  • Thanks for code. Can you add on which databases the code works? – Ram Oct 13 '14 at 03:44
  • The IF dont work. 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF my_result = 'Y'' – Darlan Dieterich Oct 24 '18 at 13:45