none
SQL UPDATE STATEMENT

    Question

  • How do update all the columns together in table. That is if a column contains string null("NULL") I want to update it to DB NULL(NULL).

    I want to achieve this with a single SQL statement without writing all the filed names in the statement. Because i have 60 to70 columns in each table.

    Thanks in advance

    Saturday, March 23, 2013 1:37 PM

Answers

  • Hi,

    Below code will generate update statements for all tables in a database that has columns that are defined as nullable,

    --note: change the schema name

    I am not sure about any other method to do this :)

    DECLARE @query NVARCHAR(MAX)
    DECLARE @table_count int,@column_count int 
    DECLARE @tablename Varchar(100),@Columnname Varchar(100)
    DECLARE @i int = 1,@j int = 1
    DECLARE @MyTableVar table(
    		Number INT IDENTITY(1,1),
    		Table_list varchar(200));
    DECLARE @MyColumnVar table(
    		Number INT IDENTITY(1,1),
    		Column_list varchar(200));
        INSERT INTO @MyTableVar
        SELECT name FROM sys.tables WHERE TYPE = 'U' AND SCHEMA_NAME(SCHEMA_ID) = 'dbo'
        SELECT  @table_count = max(Number) from @MyTableVar 
        WHILE @i <= @table_count
          BEGIN
          Select  @tablename = Table_list from @MyTableVar WHERE Number = @i
    	  INSERT @MyColumnVar
          SELECT C.name
                FROM SYS.columns C
                INNER JOIN SYS.tables T ON T.object_id = C.object_id
                INNER JOIN SYS.types TY ON TY.user_type_id = C.user_type_id AND TY.system_type_id = C.system_type_id
                WHERE SCHEMA_NAME(T.SCHEMA_ID) = 'dbo' AND OBJECT_NAME(T.OBJECT_ID) = @tablename AND T.type = 'U'
    			AND C.is_nullable = 1
                ORDER BY C.column_id 
    	  SELECT  @column_count = max(Number) from @MyColumnVar
    	  WHILE @j <= @column_count
          BEGIN
    	     Select  @Columnname = Column_list from @MyColumnVar WHERE Number = @j
    		 SET @query = 'UPDATE ['+@tablename+'] SET ['+@Columnname+']  = NULL WHERE  ['+@Columnname +'] = ''NULL'''  
             SET @j = @j + 1
       PRINT @query
         
    	 --EXEC (@query)
         END
          SET @i = @i + 1
          END


    Thanks & Regards, sathya

    Saturday, March 23, 2013 1:37 PM
    Moderator
  • Hi GopichandJ,

    here is the recent thread - same question - http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a9ac9d4c-cfe7-47e7-86ad-fbc1db482d72

    SET NOCOUNT ON
    DECLARE @query NVARCHAR(MAX)
    DECLARE @table_count int,@column_count int 
    DECLARE @tablename Varchar(100),@Columnname Varchar(100)
    DECLARE @i int = 1,@j int = 1
    DECLARE @MyTableVar table(
    		Number INT IDENTITY(1,1),
    		Table_list varchar(200));
    DECLARE @MyColumnVar table(
    		Number INT IDENTITY(1,1),
    		Column_list varchar(200));
        INSERT INTO @MyTableVar
        SELECT name FROM sys.tables WHERE TYPE = 'U' AND SCHEMA_NAME(SCHEMA_ID) = 'dbo'
        SELECT  @table_count = max(Number) from @MyTableVar 
        WHILE @i <= @table_count
          BEGIN
          Select  @tablename = Table_list from @MyTableVar WHERE Number = @i
    	  INSERT @MyColumnVar
          SELECT C.name
                FROM SYS.columns C
                INNER JOIN SYS.tables T ON T.object_id = C.object_id
                INNER JOIN SYS.types TY ON TY.user_type_id = C.user_type_id AND TY.system_type_id = C.system_type_id
                WHERE SCHEMA_NAME(T.SCHEMA_ID) = 'dbo' AND OBJECT_NAME(T.OBJECT_ID) = @tablename AND T.type = 'U'
    			AND C.is_nullable = 1
                ORDER BY C.column_id 
    	  SELECT  @column_count = max(Number) from @MyColumnVar
    	  WHILE @j <= @column_count
          BEGIN
    	     Select  @Columnname = Column_list from @MyColumnVar WHERE Number = @j
    		 SET @query = 'UPDATE ['+@tablename+'] SET ['+@Columnname+']  = NULL WHERE  ['+@Columnname +'] = ''NULL'''  
             SET @j = @j + 1
       PRINT @query
         
    	 --EXEC (@query)
         END
          SET @i = @i + 1
          END


    Thanks & Regards, sathya

    • Proposed as answer by Aalamjeet Rangi Saturday, March 23, 2013 10:38 PM
    • Marked as answer by GopichandJ Wednesday, April 3, 2013 4:57 AM
    Saturday, March 23, 2013 2:35 PM
    Moderator

All replies

  • hi,

    i have  some 20 tables and each have some 20 to 30 columns

    i need to check and update each table if string "NULL" then updated into dbnull(i.e null)

    so is there any update statement i can use...

    thanks in advance...

    Saturday, March 23, 2013 12:10 PM
  • --try like this

    UPDATE TABLE SET COlumn = NULL WHERE Column = 'NULL'

    --example

    DECLARE @TMP TABLE (ID INT IDENTITY(1,1),[COLUMN] VARCHAR(20) NULL) INSERT @TMP SELECT NULL INSERT @TMP SELECT 'NULL' SELECT * FROM @TMP UPDATE @TMP SET [COLUMN] = NULL WHERE [COLUMN] = 'NULL' SELECT * FROM @TMP




    Thanks & Regards, sathya





    Saturday, March 23, 2013 12:15 PM
    Moderator
  • I have 60 to 70 columns do i need add all the 60 columns is there any other alternative way

    to update all the columns 

    Saturday, March 23, 2013 12:38 PM
  • Hi,

    Below code will generate update statements for all tables in a database that has columns that are defined as nullable,

    --note: change the schema name

    I am not sure about any other method to do this :)

    DECLARE @query NVARCHAR(MAX)
    DECLARE @table_count int,@column_count int 
    DECLARE @tablename Varchar(100),@Columnname Varchar(100)
    DECLARE @i int = 1,@j int = 1
    DECLARE @MyTableVar table(
    		Number INT IDENTITY(1,1),
    		Table_list varchar(200));
    DECLARE @MyColumnVar table(
    		Number INT IDENTITY(1,1),
    		Column_list varchar(200));
        INSERT INTO @MyTableVar
        SELECT name FROM sys.tables WHERE TYPE = 'U' AND SCHEMA_NAME(SCHEMA_ID) = 'dbo'
        SELECT  @table_count = max(Number) from @MyTableVar 
        WHILE @i <= @table_count
          BEGIN
          Select  @tablename = Table_list from @MyTableVar WHERE Number = @i
    	  INSERT @MyColumnVar
          SELECT C.name
                FROM SYS.columns C
                INNER JOIN SYS.tables T ON T.object_id = C.object_id
                INNER JOIN SYS.types TY ON TY.user_type_id = C.user_type_id AND TY.system_type_id = C.system_type_id
                WHERE SCHEMA_NAME(T.SCHEMA_ID) = 'dbo' AND OBJECT_NAME(T.OBJECT_ID) = @tablename AND T.type = 'U'
    			AND C.is_nullable = 1
                ORDER BY C.column_id 
    	  SELECT  @column_count = max(Number) from @MyColumnVar
    	  WHILE @j <= @column_count
          BEGIN
    	     Select  @Columnname = Column_list from @MyColumnVar WHERE Number = @j
    		 SET @query = 'UPDATE ['+@tablename+'] SET ['+@Columnname+']  = NULL WHERE  ['+@Columnname +'] = ''NULL'''  
             SET @j = @j + 1
       PRINT @query
         
    	 --EXEC (@query)
         END
          SET @i = @i + 1
          END


    Thanks & Regards, sathya

    Saturday, March 23, 2013 1:37 PM
    Moderator
  • You can't. But you should be able to easily generate a sql statement that updates all of the columns you care about with a case statement for each column.


    Chuck Pedretti | Magenic – North Region | magenic.com


    Saturday, March 23, 2013 1:54 PM
  • Hi GopichandJ,

    here is the recent thread - same question - http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a9ac9d4c-cfe7-47e7-86ad-fbc1db482d72

    SET NOCOUNT ON
    DECLARE @query NVARCHAR(MAX)
    DECLARE @table_count int,@column_count int 
    DECLARE @tablename Varchar(100),@Columnname Varchar(100)
    DECLARE @i int = 1,@j int = 1
    DECLARE @MyTableVar table(
    		Number INT IDENTITY(1,1),
    		Table_list varchar(200));
    DECLARE @MyColumnVar table(
    		Number INT IDENTITY(1,1),
    		Column_list varchar(200));
        INSERT INTO @MyTableVar
        SELECT name FROM sys.tables WHERE TYPE = 'U' AND SCHEMA_NAME(SCHEMA_ID) = 'dbo'
        SELECT  @table_count = max(Number) from @MyTableVar 
        WHILE @i <= @table_count
          BEGIN
          Select  @tablename = Table_list from @MyTableVar WHERE Number = @i
    	  INSERT @MyColumnVar
          SELECT C.name
                FROM SYS.columns C
                INNER JOIN SYS.tables T ON T.object_id = C.object_id
                INNER JOIN SYS.types TY ON TY.user_type_id = C.user_type_id AND TY.system_type_id = C.system_type_id
                WHERE SCHEMA_NAME(T.SCHEMA_ID) = 'dbo' AND OBJECT_NAME(T.OBJECT_ID) = @tablename AND T.type = 'U'
    			AND C.is_nullable = 1
                ORDER BY C.column_id 
    	  SELECT  @column_count = max(Number) from @MyColumnVar
    	  WHILE @j <= @column_count
          BEGIN
    	     Select  @Columnname = Column_list from @MyColumnVar WHERE Number = @j
    		 SET @query = 'UPDATE ['+@tablename+'] SET ['+@Columnname+']  = NULL WHERE  ['+@Columnname +'] = ''NULL'''  
             SET @j = @j + 1
       PRINT @query
         
    	 --EXEC (@query)
         END
          SET @i = @i + 1
          END


    Thanks & Regards, sathya

    • Proposed as answer by Aalamjeet Rangi Saturday, March 23, 2013 10:38 PM
    • Marked as answer by GopichandJ Wednesday, April 3, 2013 4:57 AM
    Saturday, March 23, 2013 2:35 PM
    Moderator
  • If you use those tables as a source in SSIS then an alternative could be to use a Script Component that checks all columns:
    http://microsoft-ssis.blogspot.com/2010/12/do-something-for-all-columns-in-your.html

    This example uppers all string columns:

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Saturday, March 23, 2013 8:14 PM
    Moderator