Variables and Updates

[delaap]

Hi,

I have two tables in a SQL Database. ADDRESS & VARIABLE_TABLE.
The Address Table requires a flag to show whether the record is outside 12 months or not.
The Variable Table holds source id's which are used to show whether the Address rec's are outside 12 months i.e.

The script I'm trying to run should set a variable to = something like - '0002','0003','0004','0005' which comes from the Variable_Table.

DECLARE @Add_Status varchar(100)
SET @Add_Status = (SELECT RTRIM(Variable_Table.Source_id)
FROM Variable_Table
WHERE ID = '3')

UPDATE Address
SET Address_Status = 'Outside 12 Months'
WHERE Source_id in (SELECT @Add_Status)

Does anyone have any ideas why when this is run I update zero records, but when I run ........

UPDATE Address
SET Address_Status = 'Outside 12 Months'
WHERE Source_id in ('0002','0003','0004','0005')

The update works as expected.
Most Confused !

 

[codestorm]

probably because @AddStatus is not storing an array. try

UPDATE Address
SET Address_Status = 'Outside 12 Months'
WHERE Source_id in
(SELECT RTRIM(Variable_Table.Source_id)
FROM Variable_Table WHERE ID = '3') codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>