0

My program prompts the user to enter an ID and then enter a new amount for the ID they selected, when they enter a new amount it writes a new row to the database, what I want to do is when they enter that new amount I want it to delete the existing data about the user so it doesn't write the same information to the database with just a new value. 

Player updatedPlayer = null;
System.out.println("Enter the player ID:");
String playerId = FileUtility.getInput().nextLine();

System.out.println("Here are the players");
//theList = loadCampersFromDatabase(theList);
for (Player camper : PlayerDAO.selectAllById(playerId)) {
    System.out.println(camper);
    System.out.println("Enter the new amount paid");
    newAmount = FileUtility.getInput().nextInt();

    Connection dbConnection = null;
    PreparedStatement preparedStatement = null;

    String sql = "DELETE FROM Camper WHERE id = ?, INSERT INTO `Camper`(`id`, `firstName`, `lastName`, `parentsName`,`phoneNumber`,`email`,`amountPaid`) "
         + "VALUES (?,?,?,?,?,?,?)";

    try {
         dbConnection = getDBConnection();
         preparedStatement = dbConnection.prepareStatement(sql);

         preparedStatement.setInt(1, camper.getRegistrationId());
         preparedStatement.setString(2, camper.getFirstName());
         preparedStatement.setString(3, camper.getLastName());
         preparedStatement.setString(4, camper.getParentsName());
         preparedStatement.setInt(5, camper.getPhoneNumber());
         preparedStatement.setString(6, camper.getEmail());
         preparedStatement.setInt(7, MainClass.getNewAmount());

         // execute update SQL stetement
         preparedStatement.executeUpdate();

         System.out.println("Record is updated to DBUSER table!");
     } catch (SQLException e) {
         System.out.println(e.getMessage());
     } finally {
         if (preparedStatement != null) {
             preparedStatement.close();
         }

         if (dbConnection != null) {
             dbConnection.close();
         }
     }
}
Zsmaster
  • 1,549
  • 4
  • 19
  • 28
Ryan Blanchard
  • 99
  • 8
  • 7
    how about an *update*? – Timothy Truckle Oct 09 '17 at 14:46
  • 1
    With proper indexing of your table, perhaps you can use an `INSERT ... ON DUPLICATE KEY UPDATE` statement, as described in [this thread](https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists). – Ted Hopp Oct 09 '17 at 14:47
  • SQL does not allow this as a single "query". You want to use a TRANSACTION. Covered in any SQL book or class or comprehensive web tutorial. – Hogan Oct 09 '17 at 14:53
  • @TimothyTruckle I changed my sql statement to update but when I run through the code I get an error saying "No value specified for parameter 8" – Ryan Blanchard Oct 09 '17 at 15:04
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. –  Oct 09 '17 at 16:02

0 Answers0