sql - UPDATE TABLE SET (subquery) = '0' -


microsoft sql server 2012, management studio

i have need update single row table 0 values. hitch there 250 columns don't want hard code columns , column count isn't fixed , change next time run code.

i have sql code returns column names looking for.

select     column_name     information_schema.columns      table_name = 'table_name'      , column_name <> 'id'     , column_name <> 'fkid'     , column_name <> 'business' 

what i'd encapsulate query subquery update.

set (subquery) = '0' [id] = someid. 

but getting error attempting this. seems should able still error:

msg 102, level 15, state 1, line 8
incorrect syntax near '('.

msg 102, level 15, state 1, line 17
incorrect syntax near '='.

after trying:

update table_name set (select          column_name              information_schema.columns      table_name = 'table_name'         , column_name <> 'id'        , column_name <> 'fkid'        , column_name <> 'business') = '0'      id = '26524' 

i have looked around , there doesn't seem way set (subquery) = 0.

stumped.

try this:

declare @sqlcmd varchar(max) = 'update table_name set ' +   stuff( (     select         ',' + column_name + '=0'              information_schema.columns              table_name = 'table_name'          , column_name <> 'id'         , column_name <> 'fkid'         , column_name <> 'business'     xml path('') ),1,1,'') + ' somewherecrit=123;'  select @sqlcmd;  --if select shows correct command, remove double minus before exec --exec(@sqlcmd); 

Comments