Update Table Variable in SQL Server and getting Must declare the scalar variable

Question

I CAN change to temp table if need be, but when I am doing an UPDATE on a table variable in sql server, why are I getting this error and how can I fix, should I switch to temp table ?

Must declare the scalar variable "@rpmuserTableVariable".

DECLARE @rpmuserTableVariable TABLE
(
 [usr_id] [varchar](8) NOT NULL,
    [usr_fnm] [varchar](64) NOT NULL,
    [usr_lnm] [varchar](64) NOT NULL,
    [usr_pwd] [varchar](64) NOT NULL,
    [email_id] [varchar](250) NULL,
    [wwid] [varchar](8) NULL,
    [tel] [char](20) NULL,
    [dflt_ste_id] [int] NOT NULL,
    [lst_pwd_chg_dtm] [datetime] NULL,
    [lst_accs_dtm] [datetime] NULL,
    [apprvr_wwid] [varchar](8) NULL,
    [inact_ind] [varchar](1) NOT NULL,
    [cre_usr_id] [varchar](8) NOT NULL,
    [cre_dtm] [datetime] NOT NULL,
    [chg_usr_id] [varchar](8) NULL,
    [chg_dtm] [datetime] NULL,
    [salt] [varchar](20) NULL,
    STATUS [char] (1) NULL 
);

-- All Active Users  
INSERT INTO @rpmuserTableVariable
SELECT *  ,'0'                       
FROM rpm_scrty_rpm_usr WITH(NOLOCK)                                             
WHERE inact_ind = 'N'
-- Internal Users  
UPDATE @rpmuserTableVariable 
SET STATUS = 1 
FROM rpm_scrty_rpm_usr ru WITH(NOLOCK)                      
INNER JOIN rpm_scrty_emp_info ei WITH(NOLOCK)                      
       ON ru.wwid = ei.wwid                    
WHERE ru.inact_ind = 'N'                       
AND ei.inact_ind = 'N'
AND ei.dmn_addr IS NOT NULL   
AND @rpmuserTableVariable.usr_id = ru.usr_id 


select * from @rpmuserTableVariable

Do I need to use a temp table #tempblah or is there a "trick" to doing this?

Also, I CAN do a bulk update right? I do not need to do a WHILE loop do I?

Answer 1

No need. You just need a table alias. Aliases cannot start with @:

UPDATE rtv
    SET STATUS = 1 
FROM @rpmuserTableVariable rtv INNER JOIN
     rpm_scrty_rpm_usr ru WITH(NOLOCK)
     ON rtv.usr_id = ru.usr_id INNER JOIN
     rpm_scrty_emp_info ei WITH(NOLOCK)                      
     ON ru.wwid = ei.wwid                    
WHERE ru.inact_ind = 'N' AND                   
      ei.inact_ind = 'N' AND
      ei.dmn_addr IS NOT NULL;