SQL Server Performance Forum – Threads Archive
Access – Update with Inner Join
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 failIn 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;
Thanks Adriaan. It works well. What is the need for DISTINCTROW? Madhivanan Failing to plan is Planning to fail
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.
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
Ar you talking about your actual data? Can you provide some sample data?
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
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.
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
Correct, the WHERE clause comes after the SET clause.
]]>