none
Check if the column exist and then update RRS feed

  • Question

  • I am checking if the column exist and then I am trying to update the column but the script is erroring out with Invalid column error.

    IF COL_LENGTH('Table', 'Test') IS NOT NULL
    BEGIN
    	UPDATE Table SET Test = NULL
    END;

    Friday, December 6, 2019 2:50 PM

All replies

  • Don't use reserved word as your table name.

    IF COL_LENGTH('Table', 'Test') IS NOT NULL
    BEGIN
    	UPDATE [Table] SET Test = NULL
    END;

    If your table is call Table1:

    IF COL_LENGTH('Table1', 'Test') IS NOT NULL
    BEGIN
    	UPDATE Table1 SET Test = NULL
    END;

    Friday, December 6, 2019 2:55 PM
    Moderator
  • It seems that you have to use a dynamic query, for example:

       IF COL_LENGTH('MyTable', 'Test') IS NOT NULL

       BEGIN

          exec ( 'UPDATE MyTable SET Test = NULL' )

       END

    Friday, December 6, 2019 9:53 PM
  • When SQL Server compiles a batch and finds that table is missing, it stays silent hoping that the table will be there are run-time. However, for columns it is different. If a table exists, SQL Server checks that all columns in the query exists.

    If this is not what you want you need to push the UPDATE operation to an inner scope, for instance a stored procedure. Or simply dynamic SQL as Viorel suggested.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, December 6, 2019 10:52 PM
  • Hi Lio1972, 

    That happens because TABLE is a reserved keyword for T-SQL. Reserved Keywords (Transact-SQL) If you really need to use that name, your query should enclose TABLE in square brackets.

    I do some test and hope  that these example will make you clear. 

    ---IF OBJECT_ID('Table') IS NOT NULL drop table [Table]
    go 
    create table Table ( Test int )
    /*
    Msg 156, Level 15, State 1, Line 3
    Incorrect syntax near the keyword 'Table'.
    */
    
    create table [Table] ( Test int )
    /*
    Commands completed successfully.
    */
    
    UPDATE Table SET Test = NULL
    /*
    Msg 156, Level 15, State 1, Line 13
    Incorrect syntax near the keyword 'Table'.
    */
    IF  COL_LENGTH('Table', 'Test') IS NOT NULL
       BEGIN
          UPDATE [Table] SET Test = NULL
       END

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 9, 2019 9:01 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 13, 2019 9:02 AM
  • Hi,

     

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 20, 2019 7:27 AM