Strange UPDATE syntax in MS Access 2003

I've got an Access application with an update query with the following syntax:

UPDATE TABLE1, TABLE2 SET 
TABLE2.VALUE1 = TABLE1.VALUE1, 
TABLE2.VALUE2 = TABLE1.VALUE2, 
TABLE2.VALUE3 = TABLE1.VALUE3, 
TABLE2.VALUE4 = TABLE1.VALUE4

The query is working but I do not un开发者_如何学编程derstand what's going on here. I'm trying to convert this query to SQL Server. Can somebody please explain what this query does? My guess is that it's a special Access syntax.

Thanks, Sven

It uses the older implicit JOIN syntax, although SQL Server should understand that syntax too.

It's INNER JOINing table1 and table2, then moving the values from table1 to table2. Because of the lack of JOIN conditions, if table1 has more than 1 row it may have unpredictable results.

Essentially it is:

 UPDATE Table1 INNER JOIN Table2 <<ON Missing Conditions Here>>
   SET Table2.Value1 = Table1.Value1
       Table2.Value2 = Table1.Value2
       Table2.Value3 = Table1.Value3
       Table2.Value4 = Table1.Value4

You can convert this to SQL Server with something like this:

 UPDATE Table2
   SET Table2.Value1 = Table1.Value1
       Table2.Value2 = Table1.Value2
       Table2.Value3 = Table1.Value3
       Table2.Value4 = Table1.Value4
   FROM Table1 INNER JOIN Table2 <<ON Missing Conditions Here>>

Every field from TABLE2 will override corresponded field from TABLE1 with records from TABLE1 one by one. Result will be TABLE2 with all replaced records by last row from TABLE1. If TABLE1 has no records - no changes happens. Sorry for my english. And... it is SQL.

Try to avoid the "UPDATE with join" syntax in SQL Server. It is completely non-standard SQL but more seriously it gives unpredictable results without any error or warning if the joining criteria is not correct. Use the MERGE statement instead or use the standard version of the UPDATE statement (with a subquery) if you can.