SQL Server Performance

Access - Update with Inner Join

Discussion in 'General Developer Questions' started by Madhivanan, Dec 22, 2005.

  1. Madhivanan Moderator

    I tried to simulate the SQL Server update query into Access

    Update T1 set t1.name=T2.name from table1 t1 inner join table2 t2 on t1.code=t2.code

    But it gives error "Missing operator in query expression" and pointing from

    Once, I did the same type of update in Access but now forget the exact syntax

    Madhivanan

    Failing to plan is Planning to fail
  2. Adriaan New Member

    In Jet SQL, you put all tables into the UPDATE clause, as if it was the FROM clause. And often Jet requires you to include the DISTINCTROW keyword.

    UPDATE DISTINCTROW table1 t1
    INNER JOIN table2 t2 ON t1.code=t2.code
    SET t1.name=T2.name;
  3. Madhivanan Moderator

    Thanks Adriaan. It works well. What is the need for DISTINCTROW?

    Madhivanan

    Failing to plan is Planning to fail
  4. Adriaan New Member

    You need it in case the join is between two fields that also have a 1-to-Many relationship, and there are multiple matches.

    Not sure if SQL Server is clever enough to disregard the duplicates, but Jet will refuse to do the update if you don't specify DISTINCTROW.
  5. Madhivanan Moderator

    Well. It seems that if there is one-to-many relationship then the first name is updated if the code has more than one name

    Madhivanan

    Failing to plan is Planning to fail
  6. Adriaan New Member

    Ar you talking about your actual data? Can you provide some sample data?
  7. Madhivanan Moderator

    Well. I am working with some test tables

    table1 has


    codename
    1Tom
    2Foo
    3Bar
    4Terry
    5clark

    table2 has


    codename
    1Town
    2Kim
    3Sara
    4White
    5Not
    5Jim

    When I run update then table1 will have name as Not for the code 5

    Madhivanan

    Failing to plan is Planning to fail
  8. Adriaan New Member

    Okay, it seems pretty clear that indeed one value is selected as the new value on the receiving table.

    I think the main issue here is that the referring key must be unique in the lookup table. If not, you simply don't have valid lookup criteria - you're basically comparing apples and oranges.
  9. Madhivanan Moderator

    Also where condition should be used after SET if any

    UPDATE DISTINCTROW table1 t1
    INNER JOIN table2 t2 ON t1.code=t2.code
    SET t1.name=T2.name where <condition>


    Madhivanan

    Failing to plan is Planning to fail
  10. Adriaan New Member

    Correct, the WHERE clause comes after the SET clause.

Share This Page