clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36253 in the last week).

create table Budgets ( Id int primary key identity(1,1), GLLink int, Budget01 decimal(12,2), Budget02 decimal(12,2), Budget03 decimal(12,2) );
 hidden batch(es)


create table Accounts ( Id int primary key identity(1,1), AccountLink int, Master_Sub_Account varchar(30) );
 hidden batch(es)


insert into Budgets (GLLink, Budget01, Budget02, Budget03) values (100, 123, 456, 789), (100, 987, 654, 321) ;
2 rows affected
 hidden batch(es)


insert into Accounts (AccountLink, Master_Sub_Account) values (100,'3200>');
1 rows affected
 hidden batch(es)


select * from Budgets;
Id GLLink Budget01 Budget02 Budget03
1 100 123.00 456.00 789.00
2 100 987.00 654.00 321.00
 hidden batch(es)


declare @Cols NVARCHAR(max); declare @DynSql NVARCHAR(max); SELECT @Cols = concat(@Cols+', ',char(10), Col.Name, '=0') FROM SYS.OBJECTS Obj JOIN SYS.COLUMNS Col ON Obj.OBJECT_ID = Col.OBJECT_ID WHERE Obj.TYPE='U' AND Obj.NAME = 'Budgets' AND Col.Name LIKE 'Budget[0-9][0-9]'; -- select @Cols as Cols; set @DynSql = N'update Budgets set '+ @Cols + char(10) + 'where GLLink in (select AccountLink from Accounts where Master_Sub_Account like ''3200>%'')'; select @DynSql as DynSql; exec(@DynSql);
DynSql
update Budgets set Budget01=0, Budget02=0, Budget03=0 where GLLink in (select AccountLink from Accounts where Master_Sub_Account like '3200>%')
 hidden batch(es)


select * from Budgets;
Id GLLink Budget01 Budget02 Budget03
1 100 0.00 0.00 0.00
2 100 0.00 0.00 0.00
 hidden batch(es)