Refresh Column Information
I am trying to update the information in the Mgrstat column to 3 and would like to enter the information in bulk. Since I have to use "=" and enter each AppID individually, I would rather enter several at once. The query below shows my attempt using "in" which also doesn't work. I am getting "Wrong syntax near text" in. ".
Any ideas? Thanks everyone!
declare @appid as int
declare @mgrstat as int
set @appid in ('10995',
'11201',
'9523',
'9558',
'9666',
'10069',
'10547',
'10548',
'9702',
'10698',
'9754',
'10161',
'10162',
'11240',
'11241',
'9553',
'10848',
'10667',
'9383',
'10709',
'9696',
'10053',
'10702')
set @mgrstat = 3
update [Compensation].[dbo].[dev_RPT_Approval]
set Mgr_Stat = @mgrstat
FROM [Compensation].[dbo].[dev_RPT_Approval]
where @appid = App_Id
select *
from [Compensation].[dbo].[dev_RPT_Approval]
where @appid = App_Id
source to share
This is the SQL you need:
update dev_RPT_Approval set Mgr_Stat=3
where designation
in ('10995',
'11201',
'9523',
'9558',
'9666',
'10069',
'10547',
'10548',
'9702',
'10698',
'9754',
'10161',
'10162',
'11240',
'11241',
'9553',
'10848',
'10667',
'9383',
'10709',
'9696',
'10053',
'10702')
source to share
If I understand correctly, and you want all mgr_stats
to be 3, where app_id is in the list given in your question, you can do it in several ways:
update [Compensation].[dbo].[dev_RPT_Approval]
set Mgr_Stat = 3
where app_id in (
'10995',
'11201',
'9523',
'9558',
'9666',
'10069',
'10547',
'10548',
'9702',
'10698',
'9754',
'10161',
'10162',
'11240',
'11241',
'9553',
'10848',
'10667',
'9383',
'10709',
'9696',
'10053',
'10702'
)
or (sql server using table variable)
declare @ids table (id varchar(50))
insert into @ids (id)
select '10995'
union all select '11201'
union all select '9523'
union all select '9558'
union all select '9666'
union all select '10069'
union all select '10547'
union all select '10548'
union all select '9702'
union all select '10698'
union all select '9754'
union all select '10161'
union all select '10162'
union all select '11240'
union all select '11241'
union all select '9553'
union all select '10848'
union all select '10667'
union all select '9383'
union all select '10709'
union all select '9696'
union all select '10053'
union all select '10702'
update [Compensation].[dbo].[dev_RPT_Approval]
set Mgr_Stat = 3
from [Compensation].[dbo].[dev_RPT_Approval] t
inner join @ids i on t.app_id = i.id
A few notes regarding the code you posted:
declare @appid as int
set @appId in ...
A couple of things with this - @appId is declared as an integer, that is, it is a scalar value (cannot be a set) - for sets of values, you can use a table variable as it was in my second example of how to accomplish your question.
Also, since you are modifying as an int, I am assuming your identifier is of type int, no quotes are needed.
Instead:
where app_id in (
'10995',
....
)
You can do:
where app_id in (
10995,
....
)
source to share