Update Query And Correlation Names 1

[OrWolf]

When I attempt to run an update query on a table called 'Call' I receive the error "Use correlation names to distinguish them". When I update the From to specify a correlation name of MyCall and update all references of Call to read MyCall I receive the error 'Invalid object name 'MyCall'. I tried change 'UPDATE MyCall' to read 'UPDATE Call', but that fails. Any ideas?

UPDATE MyCall SET MyCall.Primary_ID = [Secondary].[Primary_ID]
FROM Call MyCall INNER JOIN MyCall ON MyCall.Secondary_ID = Secondary.Secondary_ID
WHERE ((MyCall.Secondary_ID <> '0' AND (MyCall.Primary_ID)<>[Secondary].[Primary_ID]));

 

[donutman]

You are using Access, post there.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[OrWolf]

Actually I'm using SQL Query Analyzer.

 

[Pattycake245]

You are referencing an alias name [Secondary] for a table, but what table does this alias name refer to? You alias Call as MyCall and you join on a table called MyCall, is this a typo? I would use more clear alias names. This may be why you're getting this message.

UPDATE a SET a.Primary_ID = b.[Primary_ID]
FROM Call a INNER JOIN MyCall b ON a.Secondary_ID = b.Secondary_ID
WHERE ((a.Secondary_ID <> '0' AND (a.Primary_ID)<>b.[Primary_ID]));

Tim

 

[OrWolf]

I'm still not sure what I did wrong, but your example worked when modified for my tables. Thank you very much for the assistance.

 

[Pattycake245]

Well, I think the main problem was that you were aliasing the table Call with the name MyCall, but MyCall is also the name of the joined table. My guess is that SQL was unable to figure out which fields went with which table and therefore spit out the error.

From an article online:

An exposed name is an unqualified object name which assumes the default owner and database. For example, table1 (exposed name) and master.dbo.table1 (qualified name) may not be the same table but SQL Server will think you mean the same table if you reference just table1 as dbo in the master database. Explicit correlation names allow you to make distinctions between references in a query.

Tim