how can we use where clause with insert query using preparedStatement?

[Rd Dari]

Hi everyone,


I want to insert value from a table into anoother how it is possible to insert with where caluse


my query is below:

pst= con.prepareStatement("insert into history_grocery values(0,?,?,?,?,?,?,?,sysdate())");
                 pst.setString(1,cat);
                 pst.setString(2,subcat);
                 pst.setString(3,unit_name);
                 pst.setString(4,item_rate);
                 pst.setString(5,quan);
                 pst.setString(6,total_amount);
                 pst.setString(7,username);
                 pst.executeUpdate();

and I want in this----

pst= con.prepareStatement("insert into history_grocery values(0,?,?,?,?,?,?,?,sysdate())where grocery.subcategory=history_grocery.subcategory='"+cat+"'");

cat is the dynamic.........

thank you.

[Sudheer Bhat]

Your insert statement should look like

Insert into tab1(column list) SELECT val1, val2,........ FROM tab2 WHERE ........... ;
Make sure your SELECT returns correct data types for the tab1 in the same order as column list.

[Rd Dari]

what is in this ......

pst= con.prepareStatement("insert into history_grocery values(0,?,?,?,?,?,?,?,sysdate())Select subcategory from grocery where subcategory='"+cat+"'");

out put is here ;;;;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Select subcategory from grocery where subcategory='Yellow Banana'' at line 1

I tried a lot but not done

please correct this syntax error

[Sudheer Bhat]

Your SQL query should look like Insert into tab1(column list) SELECT val1, val2,........ FROM tab2 WHERE ........... ;

[Rd Dari]

but as I need some field extra in history_grocery and some missing from grocery

it this case what i should do???

like..

CREATE TABLE `history_grocery` (                         
                   `auto_generate` int(100) NOT NULL AUTO_INCREMENT,      
                   `subcategory` varchar(100) DEFAULT NULL,               
                   `grocery_name` varchar(100) DEFAULT NULL,              
                   `units` varchar(100) DEFAULT NULL,                     
                   `price_tqty` varchar(100) DEFAULT NULL,                
                   `quantity` varchar(100) DEFAULT NULL,                  
                   `total_amount` varchar(100) DEFAULT NULL,              
                   `username` varchar(100) DEFAULT NULL,                  
                   `current_date_time` datetime DEFAULT NULL,             
                   PRIMARY KEY (`auto_generate`)                          
                 )

and

CREATE TABLE `grocery` (                                                                
           `id` int(11) NOT NULL AUTO_INCREMENT,                                                 
           `grocery_id` varchar(100) DEFAULT NULL,                                               
           `category` varchar(100) CHARACTER SET latin1 COLLATE latin1_spanish_ci DEFAULT NULL,  
           `subcategory` varchar(100) DEFAULT NULL,                                              
           `grocery_name` varchar(100) DEFAULT NULL,                                             
           `Units` varchar(100) DEFAULT NULL,                                                    
           `price_hqnt` varchar(100) DEFAULT NULL,                                               
           `price_oqnt` varchar(100) DEFAULT NULL,                                               
           `price_tqnt` varchar(100) DEFAULT NULL,                                               
           `current_date_time` datetime DEFAULT NULL,                                            
           PRIMARY KEY (`id`)                                                                    
         ) 

some values are come using ajax and js in view(jsp page)like quantity and total_amount....

[Sudheer Bhat]

you can actually pass all the values which are coming from view to the SQL statement. If you need to select one field from another table and other 10 values coming from view then your SELECT part of INSERT statement would look like SELECT ?,?,?........., col1, col2 FROM table where ....
You would set the values which you retrieved from the view using statement.set... methods.