sql server - Update multiple columns in SQL

ID : 20257

viewed : 25

Tags : sqlsql-serversql-updatesql

Top 5 Answer for sql server - Update multiple columns in SQL

vote vote

95

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.

vote vote

85

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

vote vote

70

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 
vote vote

68

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)

vote vote

55

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 

Top 3 video Explaining sql server - Update multiple columns in SQL

Related QUESTION?