To update multiple dynamic field in a single query
-
hi, I need a single qurery which satisfy the muliple dynamic fields get to be updated. 1. I am having the multiple fields in the table and I want to consider only the fieldname contanis "_edt" 2. All the fieldname("_edt") get to be updated as "some value" like "XXX" Looks like : set @sql = 'UPDATE r SET ' + c.name = ''XXX'' FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id,Record r where t.name = ''Record'' and c.name like ''%_edt''' Please help on this. Thanks, Arun
-
hi, I need a single qurery which satisfy the muliple dynamic fields get to be updated. 1. I am having the multiple fields in the table and I want to consider only the fieldname contanis "_edt" 2. All the fieldname("_edt") get to be updated as "some value" like "XXX" Looks like : set @sql = 'UPDATE r SET ' + c.name = ''XXX'' FROM sys.columns c INNER JOIN sys.tables t ON c.object_id = t.object_id,Record r where t.name = ''Record'' and c.name like ''%_edt''' Please help on this. Thanks, Arun
Something like this should work:
DECLARE @SQL nvarchar(max) = N'';
SELECT
@SQL = @SQL + N', ' + QUOTENAME(name) + N' = @value'
FROM
sys.columns
WHERE
object_id = OBJECT_ID('Record')
And
name Like '%_edit'
;SET @SQL = Substring(@SQL, 3, LEN(@SQL) - 2);
SET @SQL = N'UPDATE Record SET ' + @SQL;PRINT @SQL;
EXEC sp_executesql @statement = @SQL,
@params = N'@value varchar(10)', -- TODO: Use the correct data type and size here
@value = 'XXX';
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Something like this should work:
DECLARE @SQL nvarchar(max) = N'';
SELECT
@SQL = @SQL + N', ' + QUOTENAME(name) + N' = @value'
FROM
sys.columns
WHERE
object_id = OBJECT_ID('Record')
And
name Like '%_edit'
;SET @SQL = Substring(@SQL, 3, LEN(@SQL) - 2);
SET @SQL = N'UPDATE Record SET ' + @SQL;PRINT @SQL;
EXEC sp_executesql @statement = @SQL,
@params = N'@value varchar(10)', -- TODO: Use the correct data type and size here
@value = 'XXX';
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Tnks a lot.. it is working for me
