This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am going to share a demonstration on how to update the table data using a Subquery in the PostgreSQL.
This is a fundamental help, but I found that most of the beginners always try to find the script for Subquery or Joins.
Below is a small demonstration:
Create two sample tables for Employee:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE tbl_EmployeeDetails ( EmpID SERIAL PRIMARY KEY ,EmpName CHARACTER VARYING(50) ,TotalPerformanceCount INTEGER DEFAULT 0 ); CREATE TABLE tbl_EmployeePerformanceCounter ( PerformanceCounterID SERIAL PRIMARY KEY ,EmpID INTEGER ,PerformanceCounter INTEGER ,RecordDateTime TIMESTAMP WITHOUT TIME ZONE ,CONSTRAINT fk_tbl_EmployeePerformanceCounter_EmpID FOREIGN KEY (EmpID) REFERENCES tbl_EmployeeDetails(EmpID) ); |
Insert sample data into both the tables:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
INSERT INTO tbl_EmployeeDetails (EmpName) VALUES ('Anvesh'),('Neevan'),('Martin'),('Roy'),('Jenny'); INSERT INTO tbl_EmployeePerformanceCounter (EmpID,PerformanceCounter,RecordDateTime) VALUES (1,300,'20150101') ,(1,100,'20150201') ,(1,200,'20150301') ,(2,140,'20150101') ,(2,320,'20150201') ,(3,120,'20150101') ,(3,150,'20150201') ,(4,150,'20150101') ,(4,200,'20150201') ,(4,100,'20150301'); |
Update the count of the employee performance counter using Subquery:
|
1 2 3 4 5 |
UPDATE tbl_EmployeeDetails SET TotalPerformanceCount=T.TotalCount FROM (SELECT EmpID,SUM(PerformanceCounter) AS TotalCount FROM tbl_EmployeePerformanceCounter GROUP BY EmpID) AS T WHERE tbl_EmployeeDetails.EmpID=T.EmpID; |
The Result:
|
1 |
SELECT *FROM tbl_EmployeeDetails ORDER BY 1; |
