CODEWITHSUNDEEP
sqlpostgresql
Teletubbi-OS X
Teletubbi-OS X

Reputation: 408

Most effective UPDATE procedure for multiple tables in PostgreSQL

Example / case:

Task: Update Soldiers(Pedro A, ..., Alberto Z) from (current rank) Commander to (new rank) Captain

What's the most effective way to perform this?

Upvotes: 0

Views: 73

Answers (1)

Michał Niklas
Michał Niklas

Reputation: 54302

Use more decritive table names: soldiers instead of Table_1 and ranks instead of Table_2.

Table with ranks should have id for each rank. Name it rank_id. Such rank_id may by 1 for Seaman and 100 for Fleet Admiral. PostgreSQL has SERIAL type for such key columns and this should be type of rank_id.

In soldiers table put column with rank_id which will point to record in ranks table.

Then you can promote soldiers:

UPDATE soldiers SET rank_id=(SELECT rank_id FROM ranks WHERE rank_name='Commander') WHERE rank_id=(SELECT rank_id FROM ranks WHERE rank_name='Captain')

Upvotes: 1

Related Questions