DROP IF EXISTS VS DROP

Question

Can someone tell me if there is any difference between  DROP IF EXISTS  TABLE NAME      DROP  TABLE NAME    I am asking this because I am using JDBC template in my MVC web application  If I use DROP  TABLE NAME  the error said that Table exist  And if I use DROP IF EXISTS  TABLE NAME  it says bad SQL grammar  Can some one help

User · Answer

If no table with such name exists  DROP fails with error while DROP IF EXISTS just does nothing   This is useful if you create modifi your database with a script  this way you do not have to ensure manually that previous versions of the table are deleted  You just do a DROP IF EXISTS and forget about it   Of course  your current DB engine may not support this option  it is hard to tell more about the error with the information you provide

User · Answer

Standard SQL syntax is  DROP TABLE table name    IF EXISTS is not standard  different platforms might support it with different syntax  or not support it at all  In PostgreSQL  the syntax is  DROP TABLE IF EXISTS table name    The first one will throw an error if the table doesn t exist  or if other database objects depend on it   Most often  the other database objects will be foreign key references  but there may be others  too    Views  for example    The second will not throw an error if the table doesn t exist  but it will still throw an error if other database objects depend on it    To drop a table  and all the other objects that depend on it  use one of these   DROP TABLE table name CASCADE  DROP TABLE IF EXISTS table name CASCADE    Use CASCADE with great care

User · Answer

DROP TABLE IF EXISTS  table name    it first checks if the table exists  if it does it deletes the table while  DROP TABLE  table name    it deletes without checking  so if it doesn t exist it exits with an error

User · Answer

It is not what is asked directly  But looking for how to do drop tables properly  I stumbled over this question  as I guess many others do too   From SQL Server 2016  you can use  DROP TABLE IF EXISTS dbo Table   For SQL Server  lt 2016 what I do is the following for a permanent table  IF OBJECT ID  dbo Table    U   IS NOT NULL    DROP TABLE dbo Table     Or this  for a temporary table  IF OBJECT ID  tempdb dbo  T    U   IS NOT NULL   DROP TABLE  T

User · Answer

You forgot the  table in your syntax   drop table  table name    which drops a table   Using  drop table if exists  table name    checks if the table exists before dropping it  If it exists  it gets dropped  If not  no error will be thrown and no action be taken

[sql] DROP IF EXISTS VS DROP?

Examples related to sql

Examples related to drop-table