0

I looked around for a similar question (I'm sure there is one somewhere) but could not find one.

I have a list of IDs that for each of the IDs I need to update another column of that IDs row to the same string.

Essentially, I want something like this:

List<int> uniqueIDs;

UPDATE my_table
SET certainColumn = "foo bar"
WHERE uniqueID = uniqueIDs[0]
    OR uniqueID = uniqueIDs[1]
    ...
    OR uniqueID = uniqueID[uniqueIDs.Length-1]

I know this could be achieved by surrounding this in a for/foreach-loop, but I was wondering if there is a better way to get this done, possibly in one database connection?

Any help is greatly appreciated.

Improve this question
5
  • Your example has a line of C# followed by an SQL statement - Are you trying to achieve this in C# or in SQL? Or you want to figure out how to pass the list to SQL in one shot? Commented Jan 29, 2014 at 21:58
  • Great question. I was mostly just combining the two to better show my end goal. I would like to formulate some sql update in C# and then execute it. So the latter I guess! Commented Jan 29, 2014 at 22:00
  • Will you be updating a certainColumn for multiple uniqueID ?? and you have a list of them uniqueIDs ?? correct me if I am wrong ?? Commented Jan 29, 2014 at 22:01
  • 3
    My suggestion would be a hybrid approach. Look into table valued parameters in SQL 2008 or higher. You can pass a table (custom defined table type in SQL) from c# and then just use a simple IN clause in your WHERE clause, encapsulated in a stored procedure. Commented Jan 29, 2014 at 22:03
  • @M.Ali you're correct. Multiple rows will need the certainColumn to be updated to "foo bar." Commented Jan 29, 2014 at 22:03

3 Answers 3

Reset to default
5

Well, you could use a TVP. First, create this on your server:

CREATE TYPE dbo.UniqueIDs AS TABLE(ID INT PRIMARY KEY);

Then a stored procedure:

CREATE PROCEDURE dbo.UpdateByID
  @tvp dbo.UniqueIDs READONLY
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE t SET certainColumn = 'foo bar'
    FROM dbo.my_table AS t
    INNER JOIN @tvp AS tvp
    ON t.uniqueID = tvp.ID;
END

Or:

CREATE PROCEDURE dbo.UpdateByID
  @tvp dbo.UniqueIDs READONLY
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE t SET certainColumn = 'foo bar'
    FROM dbo.my_table AS t
    WHERE EXISTS (SELECT 1 FROM @tvp
      WHERE ID = t.uniqueID);
END

Then instead of a List use a DataTable to hold your IDs in your C# application, and call the stored procedure, passing @tvp as a Structured parameter. I have simple examples of the C# side posted all over this site:

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2 
var query = "UPDATE my_table
SET certainColumn = 'foo bar'
WHERE uniqueID in (" + String.Join(",", uniqueIDs) + ")"
Improve this answer

4 Comments

Does anyone know if there is a character limit on the length of a query string? There is a possibility (but not likely) of up to about 5000 updates, which would mean (5000 * 2) extra characters in the query counting the commas. Hopefully not, because I like this solution.
Just because you can create a SQL string that is 100 KB and has an IN list of 5000 items doesn't mean you should IMHO.
@njenson I can think of quite a few limits. My advice is to create a temporary variable using good practice and then use a where clause, like a proper programmer should do
I do agree with the two above comments. Though it all depends on your scenario. If it's not really an important application I would just leave it like this, and my feeling is that it isn't, because who would still be concatenating strings for any serious app???
1

I guess you could do something like this. Get your List of Ids in some TempTable or table variable and use IN operator in your update statement. something like this ..... 

UPDATE my_table
SET certainColumn = 'foo bar'
WHERE uniqueID IN (SELECT uniqueID 
                   FROM @List_Table)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.