CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlin-clause
Albert
Albert

Reputation: 1394

Problem updating table using IN clause with huge list of ids

Hi I am having a problem when trying to update a table using an IN clause, I have a big list of clients that should be updated 4500+.

Update table 
set columnA = 'value'
where ID in ( biglistofids )  //biglistofids > 4500 ids

I am getting this error "String or binary data would be truncated."

I tried the same script with fewer ids lets say (2000) and it worked fine.

I have also tried using a temporal table but I got same error.

  1. SELECT Id INTO tmpTable FROM dbo.table WHERE id IN (biglistofids) //create temporal table succesfully
  2. Update table set columnA = 'value' FROM table INNER JOIN tmpTable ON table.ID = tmpTable.ID

Is there any way to handle this, without repeating code for each 2000 records?

Thanks in advance

Upvotes: 1

Views: 613

Answers (2)

GSerg
GSerg

Reputation: 78190

The "String or binary data would be truncated." has nothing to do with the IN clause.

It means in this line:

set columnA = 'value'

you are setting columnA to something that is too long to be held in columnA.

Maybe certain ids have corresponding data that is too long, and these are not among the first 2000 you have tried.

Upvotes: 7

AllenG
AllenG

Reputation: 8190

It looks to me, based on your error, that the actual problem is with one or more of the values you're updating. I'd try validating the input, first. I've done this many ways based on number of records I had, size of the value, type of value, etc., so that will depend on your specific scenario.

The most straight-forward one (not necessarilly the best) is the one you describe. Try to do 2000. If that works, try the next 2000, etc. That is time intensive and clunky and may not be the best for your situation, but I've never seen it fail to identify my problem.

Upvotes: 0

Related Questions