Update column information

Question

I am trying to update information in the column Mgrstat to a 3 and would like to mass enter the information. As it is I have to use "=" and enter each AppID individually but I would rather enter several at once. The query below shows my attempt using "in", which didn't work either. I get "Incorrect syntax near the keword '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

The way you've worded the question is throwing me a bit, so just to clarify, are you attempting to set all `mgr_stat`s to 3 where the app_id is in the list of appIds in `@appId`? — Kritner, May 14 '15 at 15:18
This is SQL. What I'm trying to do is build the query so that I don't have to enter each App_ID individually since I have a large number. The I included is not all inclusive. — MHeath, May 14 '15 at 15:43
"SQL" is not an RDBMS, lots of RDBMSes use sql. Is it sql server, mysql, postgres, oracle? It looks like sql server, but your question is not tagged as such (and should be) — Kritner, May 14 '15 at 15:44

score 3 · Accepted Answer · answered May 14 '15 at 15:22

3

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')

answered May 14 '15 at 15:22

Tirma

664
5
16

Hi @MitchellHeath if this or any answer has solved your question please consider [accepting it](http://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. – AHiggins May 14 '15 at 17:40

Kritner · Answer 2 · 2015-05-14T17:43:54.743

If i'm understanding correctly, and you want all mgr_stats to be 3 where the app_id is in the list provided in your question, then you could do this a few 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 things to note about the code you had posted:

declare @appid as int
set @appId in ...

A few things with this - @appId is declared as an integer, meaning it is a scalar value (cannot be a set) - for sets of values, you can use a table variable as I did in my second example of how to accomplish your question.

Additionally, because you variable as an int, I'm assuming your ID is of type int, the quotes are not needed.

Instead of:

where app_id in (
'10995',
....
)

you can do:

where app_id in (
10995,
....
)

score -1 · Answer 3 · answered May 14 '15 at 16:21

-1

can you try this? Might work for you dude. Here you are passing multiple values using "=" instead "IN"

  update [Compensation].[dbo].[dev_RPT_Approval]
    set Mgr_Stat = @mgrstat
     FROM [Compensation].[dbo].[dev_RPT_Approval]
      where App_Id IN(@appid)

answered May 14 '15 at 16:21

Chiragkumar Thakar

3,616
5
37
49

this will not work, note OPs variable `@appid` is a scalar, and cannot contain multiple values. – Kritner May 14 '15 at 17:15

Update column information

3 Answers3