How to insert values from one table into another in PostgreSQL?

PostgreSQLDatabaseData Storage

Suppose you have two tables: marks and student_info. Examples are given below for the two respectively

nameroll_noperc_marks
Aniket1224
Siddhi4565
Yash2642
Isha5687

nameroll_noagegender
Aniket1226M
Isha5625F
Siddhi4523F
Yash2625M

Now, suppose your manager at work looks at the two tables and tells you, “Why do we have two tables? Simplify things, shift everything to one table!”

So you decide to add the perc_marks column to the student_info table.

ALTER TABLE student_info
ADD COLUMN perc_marks integer

Now, how will you populate this column? Will you manually add the marks for each column? That will leave the room open for a lot of errors and will also be very time-consuming. Instead, this is what you could do −

UPDATE student_info
SET perc_marks = marks.perc_marks FROM marks
WHERE student_info.roll_no = marks.roll_no

Here, the roll_no is used as the common field between the two tables and update values for marks in the student_info table. Please note that the assumption here is that there is only one entry for each roll_no in the marks table. If there are multiple rows having the same roll_no in the marks table, it will confuse PostgreSQL. Generally, a primary key/ foreign key is used as the common field, to avoid this confusion.

Now, if you query the student_info table (SELECT * from student_info), you will see the following output −

nameroll_noagegenderperc_marks
Aniket1226M24
Isha5625F87
Siddhi4523F65
Yash2625M42

As you can see, the values of perc_marks from the marks table have been successfully added to student_info.

Published on 02-Feb-2021 17:00:26