Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Stevens Point, WI
    Posts
    1

    Question Unanswered: Dynamically delete entire columns' values

    Hello all,
    I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data.

    The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL.

    I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL.

    If anyone has any idea how to do this, I would greatly appreciate it.

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This smells of dynamic SQL and queries against system tables to me.

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you implement your solution through a stored procedure, - it'll actually not require a dynamic SQL. But let's get something straight and agree to it, - it's an UPDATE we're talking about here, not a DELETE. Probably something like this would work:
    Code:
    create procedure dbo.sp_clear_fields (
       @string varchar(8000) = null,
       @delimiter char(1) = ','				)
    as
       if @string is null return (0)
       declare @spos int, @pos int
       declare @tbl table (
    	  RecordID  int identity(1,1) not null primary key clustered,
    	  [Field]  varchar(128)   not null		   )
       set @string = replace(
    	  replace(@string, @delimiter + ' ', @delimiter), ' ' + @delimiter, @delimiter)
       set @spos = 1
       set @pos = 100
       while (@pos) > 0 begin
    	  set @pos = charindex(@delimiter, @string, @spos)
    	  insert @tbl ([Field])
    		 select
    			ltrim (
    			   rtrim (
    				  substring (
    					 @string, @spos,
    						case
    						   when (@pos - @spos) <= 0 then datalength(@string) + 1
    						   else @pos
    						end - @spos
    				  )
    			   )
    			)
    	  set @spos = @pos + 1
       end
       update a
    	  set Field1 = case when [Field] = 'Field1' then null else Field1 end,
    	  Field2 = case when [Field] = 'Field2' then null else Field2,
    	  Field3 = case when [Field] = 'Field3' then null else Field3,
    	  Field4 = case when [Field] = 'Field4' then null else Field4,
    	  Field5 = case when [Field] = 'Field5' then null else Field5
       from TableA a, @tbl
       if @@error != 0 raiserror ('Failed to clear the fields!', 15, 1)
       return (0)
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm with you thinking the question means that they want an update to NULL, but I think because of the dynamic nature of the query (the exrpessed need to pick up new columns that are added after the fact) that dynamic SQL is required. Even if it wasn't required because of that, I would find it preferable just because of the much lower load on the server (one pass through the table as opposed to one pass for each column being NULLed). I'd use something like:
    Code:
    CREATE TABLE tMtrebs (
       MtrebsID		INT		NOT NULL
       CONSTRAINT XPKtMtrebs
          PRIMARY KEY (MtrebsID)
    ,  col1			VARCHAR(20)	NULL
    ,  col2			INT		NULL
    ,  col3			DATETIME	NULL
    ,  col4			VARCHAR(20)	NULL
    ,  col5			DATETIME	NOT NULL
    ,  col6			INT		NOT NULL
       )
    GO
    
    DECLARE @cCmd		VARCHAR(500)
    
    SELECT @cCmd = Coalesce(@cCmd + ', [' + sc.name + '] = NULL'
    ,  'UPDATE tMtrebs SET [' + sc.name + '] = NULL')
       FROM dbo.syscolumns AS sc
       WHERE  Object_id('dbo.tMtrebs') = sc.id
          AND sc.name NOT IN ('col4', 'col5')	-- protected columns
          AND 1 = ColumnProperty(Object_Id('dbo.tMtrebs'), sc.name, 'AllowsNull')
    
    EXECUTE (@cCmd)
    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I agree that this makes no sense
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    I agree that this makes no sense
    Oh, and since when have minor details like that bothered any of us in the least?!?!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •