Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    42

    Unanswered: Using "In" for updates

    As far as performance goes should I avoid using "IN" with update statements?

    Example:

    update table_name
    set x = 5
    where y IN (select z from table_name1 where a = b and c = d)

    If this is terribly inefficient what are the alternatives?

    Thanks...
    Last edited by errodr; 06-19-07 at 11:12.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why would it be terribly inefficient?

    the only thing i can see is that you are referencing the same table in the subquery -- if that is true, then yes, there is a better way

    update table_name
    set x = 5
    where y = z and a = b and c = d
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would recommend using:
    Code:
    UPDATE table_name
       SET x = 5
       WHERE EXISTS (SELECT *
          FROM  table_name1
          WHERE  a = b
             AND c = d
             AND y = z)
    In Microsoft SQL, the entire subselect set is produced, then scanned for the existance of the y value. The exists clause uses indexes (if they are available) to quickly see if there is any row that meets the criteria, then returns without the need to generate the whole result set or to scan it.

    -PatP

  4. #4
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    EXISTS and NOT EXISTS are better than using IN or NOT IN as far as performance is concerned.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •