Avoiding Subquery in UPDATE

  • Is there any alternative of the following query without using subquery.

    UPDATE   TableA

    Set         ColumnA = 'Test'

    WHERE    ColumnB  = (SELECT ColumnC

                                  FROM TableB

                                  WHERE ColumnD = @Name)

    HERE 'ColumnB' of 'TableA' is the FK from 'columnC' of 'TableB'

     

     

  • UPDATE TableA

    Set ColumnA = 'Test'

    from TableA join TableB on

    TableA .ColumnB = TableB.ColumnC

    WHERE TableB.ColumnD = @Name

    I think this Query should work for you

  • wat do u think which query will give better performance.

  • also tell me is there any way to update the columns of more than one table in a single Update statement.

  • Join is always faster compare to use of any sub query.

    And about second quote... I don't think so that you can update multiple table in single Query

  • I believe that the join will take longer because the other will simply rasie an error without actually doing any updates.

    You can update multiple tables with a single Update.  There are some restrictions and things that you must set up ahead of time. 

    You will need to create an "updatable view".  From the BOL on "updatable views":

     The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.

    If the server can't unambiguously figure out what is to be updated, you may need to use an INSTEAD OF trigger.

    Excerpt from the BOL on "INSTEAD OF triggers":

    "The primary advantage of INSTEAD OF triggers is that they allow views that would not be updatable support updates. A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. Another advantage of INSTEAD OF triggers is that they allow you to code logic that can reject parts of a batch while allowing other parts of a batch succeed."

    These two excerpts seem to contradict each other.  The first claims that you can update some types of multi-table views, and the second claims that you con only do that with INSTEAD OF triggers.

    I've never tried it myself.  Let us know what you figure out!

    jg

     

  • There is an error.

    You are updating whole TableA, not only rows matched.

    UPDATE A

    Set ColumnA = 'Test'

    from TableA A

    inner join TableB on A.ColumnB = TableB.ColumnC

    WHERE TableB.ColumnD = @Name

    OR

    UPDATE TableA

    Set ColumnA = 'Test'

    from TableB

    WHERE TableA .ColumnB = TableB.ColumnC

    AND TableB.ColumnD = @Name

    _____________
    Code for TallyGenerator

  • Sergiy , your first Query and My Query is almost same only difference is you have added alias and use that Alias in update statement.

    Tell me performance wise is there any difference or does both Query will give diff result...??

  • Update TableA

    SET...

    FROM TableA

    Those Tablea's are actually DIFFERENT tables!

    And you don't have anything to join them in your query.

    That's why your result will not be the same as you expect.

    _____________
    Code for TallyGenerator

  • JG can u plz tell me wat error will be raised by the "other query"

  • Thanx Sergiy for clearing me

  • OOPS.  I read it wrong.  My apologies.

    Must drink more coffee before replying.

     

Viewing 12 posts - 1 through 11 (of 11 total)

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