Multiple table updates - avoiding multiple WHERE clauses

Question

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.

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? — Dmitriy Khaykin, Jan 29 '14 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! — Nate Jenson, Jan 29 '14 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 ?? — M.Ali, Jan 29 '14 at 22:01
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. — Dmitriy Khaykin, Jan 29 '14 at 22:03
@M.Ali you're correct. Multiple rows will need the certainColumn to be updated to "foo bar." — Nate Jenson, Jan 29 '14 at 22:03

score 5 · Accepted Answer · edited May 23 '17 at 12:21

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:

score 2 · Answer 2 · edited Jan 29 '14 at 22:07

2

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

edited Jan 29 '14 at 22:07

M.Ali

67,945
13
101
127

answered Jan 29 '14 at 22:07

Andrei Dvoynos

1,126
1
10
32

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. – Nate Jenson Jan 29 '14 at 22:11
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. – Aaron Bertrand Jan 30 '14 at 00:00
1

@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 – jcolebrand Jan 30 '14 at 00:00
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??? – Andrei Dvoynos Jan 30 '14 at 01:23

score 1 · Answer 3 · answered Jan 29 '14 at 22:05

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)

Multiple table updates - avoiding multiple WHERE clauses

3 Answers3