insert last id of column in another column table PostgreSQL

Question

I'm trying to insert into a column code from table calcul the last id of column code from table stock, this is my request which doesn't work.

  String sql1="INSERT INTO calcul (idproduit,inventaire,consomation,date,quantite,designation,dateper,ppa,tr,net,code) "
           + "VALUES ('"+codeP+"',"+newQuant+","+0+",'"+datestock+"',"+newQuant+",'"+designation+"','"+datePer+"',"+PPA+","+TR+","+NET+",SELECT MAX(code) from stock );";
     
       stmt.executeUpdate(sql1);

Please the table definitions of the two tables. Are you trying to do this after you have added a value to the `stock` table? Or independently? If you are doing this after adding a value to the stock table, are you using the same connection object for that and for this statement? Please add the relevant code. — RealSkeptic, Nov 17 '15 at 15:04
Why are you doing that in the first place? What is the underlying problem you are trying to solve? If the `code` value was generated with a sequence, use the `lastval()` function. — , Nov 17 '15 at 15:46

score 0 · Accepted Answer · edited May 23 '17 at 11:51

You need to modify your query to include a sub-query (replace values). I am not going to duplicate answers from across the stackoverflow in here however, I would like to give you reference to a few questions that already got answers, see here and here

In your, I would write the query as follow:

"INSERT INTO calcul (idproduit,inventaire,consomation,date,quantite,designation,dateper,ppa,tr,net,code) SELECT '"+codeP+"',"+newQuant+","+0+",'"+datestock+"',"+newQuant+",'"+designation+"','"+datePer+"',"+PPA+","+TR+","+NET+",MAX(code) from stock;

Your query is a bit messy, so I want to put it in more generic form below

INSERT INTO calcul (col1, col2, ....,coln) SELECT col1, col2, ..., MAX(code) FROM stock;

insert last id of column in another column table PostgreSQL

1 Answers1