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”

Table: “Teams”

Id (PK)
TeamFilm
1Justice LeagueDC
2The AvengersMarvel

Table: “Superheroes”

Id (PK)
NameFilmTeam
1SupermanDC?
2The FlashDC?
3BatmanDC?
4Wonder WomenDC?
5CyborgDC?
6Captain AmericaMarvel?
7IronmanMarvel?
8SpidermanMarvel?
9ThorMarvel?
10HulkMarvel?

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;

UPDATE Superheroes
SET Team=t.Team
FROM Teams t
INNER JOIN Superheroes s ON s.Film=t.Film

#Result

Table: “Superheroes”

IdNameFilmTeam
1SupermanDCJustice League
2The FlashDCJustice League
3BatmanDCJustice League
4Wonder WomenDCJustice League
5CyborgDCJustice League
6Captain AmericaMarvelThe Avengers
7IronmanMarvelThe Avengers
8SpidermanMarvelThe Avengers
9ThorMarvelThe Avengers
10HulkMarvelThe 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.

0 Please encourage me.
(Visited 28 times, 1 visits today)

1 Comment

  1. T August 26, 2019 at 2:16 AM

    very very useful. thx mate.

    Reply

Leave A Comment

Your email address will not be published.