Post reply

UPDATE statement, is there a limit?

  • March 29, 2007 at 1:13 pm

    #123264

    I have an UPDATE statement and the WHERE clause is something like,

     WHERE Letters IN ('a', 'b', 'c', 'zz', cc', dd', f', g')

    for example.  Is there a limit to the number of bytes in the where condition can be? Or the number of conditions for the where clause?

    For SQL 2000 and 2005?

  • March 29, 2007 at 2:21 pm

    #697748

    If there is I never hit it.  What problem are you encountering?

  • March 29, 2007 at 2:34 pm

    #697751

    like above, I've never hit a limit on the number of values in the IN() statement, and read it gets converted to a temp table by SQL Server;

    I've gotten an error in QA that said "not enough memory to perform this query" on something with a whopper IN() statement, but dumping it to an ADODB.Command object  it would still run; the error was from QA and not from SQL server.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 29, 2007 at 7:38 pm

    #697787

    No problems, just want to make sure there is no limitation.

  • March 30, 2007 at 3:17 am

    #697822

    I have seen people report issues with very large IN lists that were fixed by explicitly loading the IN list data into a table and doing a join.   I cannot remember what the threshold for this was...

    I think SQL needs to use memory to convert large IN lists into a table, so the threshold for this failing could depend on what memory is on the box.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • March 30, 2007 at 9:36 am

    #697881

    We have ran into web page limitations, but not SQL. We have ran statements that were over 7000 characters in length, with about 99% of that statment being one IN () clause. The limit through the browser was around 8000 if I remember correctly.

    Live to Throw
    Throw to Live
    Will Summers

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply