As a normal way, when you have to update some values into SQL table, you must execute a single UPDATE statement. Update one row per one statement as the example below;
UPDATE Superheroes SET Film = ‘DC’ WHERE Name = ‘Superman‘;
UPDATE Superheroes SET Film = ‘DC’ WHERE Name = ‘The Flash’;
UPDATE Superheroes SET Film = ‘Marvel’ WHERE Name = ‘Ironman‘;
In the other way, if you have designed your SQL table with a relationship between Table by link some values together (if any), you will able to use a multiple UPDATE statement. See a case study as below;
Assumption: We have two tables, the first one for “Team”, another one is “Superheroes” and we have to update all values in field “Superheroes.Team”
As a normal way mentioned, this case we can successfully update by execute 2 UPDATE statements onto “Superheroes.Team”.
UPDATE Superheroes SET Team = ‘Justice League’ WHERE Film = ‘DC’;
UPDATE Superheroes SET Team = ‘The Avengers’ WHERE Film = ‘Marvel’;
But it seem like inappropriate way for a big system or huge data updates. Because of every time we execute statement, it means the system will make a connection, make a resource allocation, and other things to response the request. So, 10 statement executes affect to 10 requests, 10 requests affect to 10 multiple resource usage.
However, if we can consider a relation between Tables, we can use multiple UPDATE statement by a single statement. This case we design a SQL statement as follow;
FROM Teams t
INNER JOIN Superheroes s ON s.Film=t.Film
|2||The Flash||DC||Justice League|
|4||Wonder Women||DC||Justice League|
|6||Captain America||Marvel||The Avengers|
The statement above we define relationship between Tables by Superheroes.Film and Teams.Film.
Just one statement, single connection, and also single request, single resource.
Finally, the result are acceptable.
NOTE: Case study test passed by MSSQL, and applicable to MySQL, PostgreSQL.