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!This smells of dynamic SQL and queries against system tables to me.

-PatP|||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:
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|||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: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|||I agree that this makes no sense|||I agree that this makes no senseOh, and since when have minor details like that bothered any of us in the least?!?!

-PatP