Inserting SUM of one column from one table to another column of another table in JAVA

Question

I have F_table and S_table in my database.I want to insert the sum of all of the values of the Quantity_column of my F_table into Total_column of the S_table. For doing that what I have to write in JAVA?

Suppose, I have two table one is Product_table and another is Stock_table.In the Product_table there are Columns like ID,Name,Quantity. And in the Stock_table there columns like ID,TotalProduct. Now I want the sum of all of the raw of Quantity Column of Product_table and insert it into the TotalProduct Column of Stock_table. When each time I add any product and its quantity then the TotalProduct will increase automatically. For getting that what I have to do in JAVA?

Sorry if it is a newbie type question. I am actually a beginner. It will be great if anyone help me.

Answer 1

If I'm understanding correctly you want to get the total quantity for each ID from the product table, and insert those into the stock table, yes?

The below will get you the sum of the quantity, grouped by the id:

select id, sum(quantity) from product_table group by id;

So if you have :

1, test1, 5
1, test2, 10
2, test3, 20
2, test4, 1
3, test5, 5

you would get something like:

1, 15
2, 21
3, 5

Then just insert these into the correct table.

To combine the two you should be able to do something like:

insert into stock_table 
select id, sum(quantity) from product_table group by id;

Keep in mind this is entirely an SQL problem that you're asking about. But to make it jdbc/java related, the jdbc for something like this would be:

String sql = "insert into stock_table select id, sum(quantity) from product_table group by id";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.executeUpdate();

stmt.close();

You would probably want to check the number of rows updated/inserted by stmt.executeUpdate().