I am using a temp table to insert some values. Now based on those values in the temp table I am trying to update my original permanent table. Everything seems right as far as the values for the variables are concerned but the update is not happening. It is correct syntactically also but the update is not happening. I am new to programming and not sure if I can assign one variable to another even if they hold specific values.
select i_stage into #temp_stage1 from riv_stage_ac where @var1 is null
update riv_stage_ac set @var1=@var2 where i_stage in (select i_stage from #temp_stage1)
@var1 holds the column name 'allow_buy' and @var2 holds 'Y'
No you cannot specify a variable column name in this way, you’ll need to use dynamic sql but take care that you don’t open yourself for hacking via SQL injection
If @var1 is unset your select will get all records as it translates to
select i_stage into #temp_stage1 from riv_stage_ac where null is null
which evaluates to true for all records and if it is set to ‘allow_buy’ as you say then there should be no records in the temp table as the value ‘allow_buy’ is not a null value
And in the update; If there are 20 records that qualify your where then your update will 20 times set the variable @var1 to ‘Y’, assuming @var2 is set to ‘Y’
You don’t need a temp table; here is some code for you to play with.
create table #riv_stage_ac (rivkey int, i_stage int, allow_buy char(1) null)
insert into #riv_stage_ac values (1,1,'N')
insert into #riv_stage_ac values (2,1,null)
insert into #riv_stage_ac values (3,2,null)
insert into #riv_stage_ac values (4,1,'Y')
insert into #riv_stage_ac values (5,3,'N')
declare @var1 varchar(10), @var2 varchar(1)
select * from #riv_stage_ac where @var1 is null -- all records
select @var1='allow_buy', @var2='Y'
select * from #riv_stage_ac where @var1 is null -- no records
exec ('select * from #riv_stage_ac where '+@var1+' is null') -- wat you wanted
-- and the update without a temp table
update #riv_stage_ac set '+@var1+'='''+@var2+'''
from #riv_stage_ac a, #riv_stage_ac b
and b.'+@var1+' is null
select * from #riv_stage_ac order by rivkey