Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2002
    Posts
    20

    Unanswered: Update with multi tables

    Hi,

    Unable to get the syntax right...

    I want to do something like

    update tblA
    set tblA.myKey = 10
    from tblB
    where tblA.id = tblB.id

    but it dont work, what do I need to do???

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Try this:

    update tblA
    set tblA.myKey = 10
    where exists (
    select 1 from tblB
    where tblA.id = tblB.id);

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Mar 2002
    Posts
    20
    Thanks!

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Purely for performance issues, also try the following.

    update tblA
    set tblA.myKey = 10
    where tblA.id in ( select id from tblB )

    It will achieve the same end result as LKBrwn_DBA's reply, but as a rule, any instance of (select 1 from ... ) should initiate some checking for better access paths.

    It's alway context dependant, it may produce better or worse results.

    Hth
    Bill

  5. #5
    Join Date
    Mar 2002
    Posts
    20
    Hi,

    What if I want to check across multiple keys?

    e.g.

    update tblA
    set tblA.myKey = 10
    from tblB
    where tblA.id = tblB.id
    and tblA.key1 = tblB.key1
    and tblA.key2 = tblB.key2

    cheers

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi

    Then this should do it

    update tblA
    set tblA.myKey = 10
    where (tblA.id, tblA.Key1, tblA.Key2) in ( select id,key1,key2 from tblB )

    Hth
    Bill

Posting Permissions

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