Upgrade query with inner joins

[darkvalley]

Hello all,

In MsAccess 2003 I was able to do an update query using inner joins.

For example:
UPDATE user INNER JOIN occupation ON user.occupationId = occupation.occupationId
SET user.name = "henk", occupation.occupationJobName = "testJob"
Where user.userId = 1

However, in sql server and sql server 2005 (using for example a stored procedure) this is not possible. To update two columns in two logically joined tables I need to fire two queries.

For example:
UPDATE user
SET name = "henk"
WHERE userID = 1
go

UPDATE occupation
SET occupationJobName = "testJob"
WHERE occupationId = 15
go

Now I was wondering if the inner join is possible to create for an update statement in SQL server. (As mentioned, this is possible in msaccess 2003)

Thanx in advance for any suggestions or comments on why one is better than the other.

[skhanal]

You can only update columns in one table in update statement. But it is possible to use JOINs in update statement as

UPDATE user
SET user.name = "henk"
from user INNER JOIN occupation ON user.occupationId = occupation.occupationId
Where user.userId = 1

[darkvalley]

skhanal, that I know but I am still wondering why Microsoft decided to remove the inner join on an update statement. In Access it is possible. I wonder if it has to do with complexity or overhead to the database query engin.

[skhanal]

I am surprised that Access supports it, it is not ANSI compliant and it is not a good practice to update columns from two tables in one statement.