How can I use the INSERT FROM SELECT of Postgresql for updating a table?

In the context of SQL, you can easily update the monthly table by using the INSERT FROM SELECT statement in Postgresql by using the ON CONFLICT clause. Here is the step given:-

Identification of the time frame

Firstly you would need to determine the specific month for which you want to update the monthly table.

Aggregation of the data from the sales table

You can summarise the data for the specific month.

Insert data into the monthly table

Now you can use the INSERT FRONLM SELECT statement with the “ON CONFLICT” clause to insert new records or even update the existing ones.

Here is the SQL query given of how you can update the monthly SQL table:-

INSERT INTO monthly_sales_summary (product_id, year_month, total_sales_amount, total_number_of_sales, total_quantity_sold)

SELECT

    Product_id,

    To_char(sale_date, ‘YYYY-MM’) AS year_month,

    SUM(total_price) AS total_sales_amount,

    COUNT(*) AS total_number_of_sales,

    SUM(quantity) AS total_quantity_sold

FROM

    Sales

WHERE

    Sale_date >= ‘2024-04-01’ AND sale_date < ‘2024-05-01’

GROUP BY

    Product_id, to_char(sale_date, ‘YYYY-MM’)

ON CONFLICT (product_id, year_month)

DO UPDATE SET

    Total_sales_amount = EXCLUDED.total_sales_amount,

    Total_number_of_sales = EXCLUDED.total_number_of_sales,

    Total_quantity_sold = EXCLUDED.total_quantity_sold;

Here is also the java code example given below for connecting with the Postgresql database and implementing the INSERT FROM SELECT statement. However, using Java code first you should have the JDBC driver in your classpath or you can add it as a dependency in your “pom.xml” if you are using maven:-

    org.postgresql

    postgresql

    42.2.20

Here is the java code given for connecting the Postgresql database and implementing the INSERT FROM SELECT statement:-

Import java.sql.Connection;

Import java.sql.DriverManager;

Import java.sql.PreparedStatement;

Import java.sql.SQLException;

Public class UpdateMonthlySalesSummary {

    Private static final String URL = “jdbc:postgresql://localhost:5432/your_database”;

    Private static final String USER = “your_username”;

    Private static final String PASSWORD = “your_password”;

    Public static void main(String[] args) {

        Connection conn = null;

        PreparedStatement pstmt = null;

        String insertFromSelectSQL = “INSERT INTO monthly_sales_summary “ +

                “(product_id, year_month, total_sales_amount, total_number_of_sales, total_quantity_sold) “ +

                “SELECT product_id, to_char(sale_date, ‘YYYY-MM’) AS year_month, “ +

                “SUM(total_price) AS total_sales_amount, “ +

                “COUNT(*) AS total_number_of_sales, “ +

                “SUM(quantity) AS total_quantity_sold “ +

                “FROM sales “ +

                “WHERE sale_date >= ‘2024-04-01’ AND sale_date < ‘2024-05-01’ “ +

                “GROUP BY product_id, to_char(sale_date, ‘YYYY-MM’) “ +

                “ON CONFLICT (product_id, year_month) “ +

                “DO UPDATE SET “ +

                “total_sales_amount = EXCLUDED.total_sales_amount, “ +

                “total_number_of_sales = EXCLUDED.total_number_of_sales, “ +

                “total_quantity_sold = EXCLUDED.total_quantity_sold;”;

        Try {

            // Establish the connection

            Conn = DriverManager.getConnection(URL, USER, PASSWORD);

            // Create a PreparedStatement

            Pstmt = conn.prepareStatement(insertFromSelectSQL);

            // Execute the query

            Int affectedRows = pstmt.executeUpdate();

            System.out.println(“Affected rows: “ + affectedRows);

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            Try {

                // Close resources

                If (pstmt != null) pstmt.close();

                If (conn != null) conn.close();

            } catch (SQLException ex) {

                Ex.printStackTrace();

            }

        }

    }

}