sql server - Update multiple columns in SQL

Try this:

UPDATE table1  SET a = t2.a, b = t2.b, ....... FROM table2 t2 WHERE table1.id = t2.id 

That should work in most SQL dialects, excluding Oracle.

And yes - it's a lot of typing - it's the way SQL does this.

The "tiresome way" is standard SQL and how mainstream RDBMS do it.

With a 100+ columns, you mostly likely have a design problem... also, there are mitigating methods in client tools (eg generation UPDATE statements) or by using ORMs

Syntax

UPDATE table-name  SET column-name = value, column-name = value, ... WHERE condition 

Example

UPDATE school SET course = 'mysqli', teacher = 'Tanzania', student = 'you' WHERE id = 6 

The Update table1 set (a,b,c) = (select x,y,x) syntax is an example of the use of row-value constructors, Oracle supports this, MSSQL does not. (Connect item)

Your query is nearly correct. The T-SQL for this is:

UPDATE  Table1 SET     Field1 = Table2.Field1,         Field2 = Table2.Field2,         other columns... FROM    Table2 WHERE   Table1.ID = Table2.ID