Update multiple columns in SQL

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


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.


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