Insert data and if already inserted then update in sql

Question

I simply want to insert the data to a SQL database table and if there is some data inserted already then I want to update that data. How can I do this using Java. Kindly help me, and sorry for bad English in advance.

Answer 1

The standard SQL statement for INSERT (if new) or UPDATE (if exists) is called MERGE.

Since you didn't specify which DBMS dialect you're asking about, I'll refer you to the Wikipedia article "Merge (SQL)", which covers most DBMS dialects. Summary:

MERGE INTO tablename USING table_reference ON (condition)
WHEN MATCHED THEN
  UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN
  INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])

Database management systems Oracle Database, DB2, Teradata, EXASOL, CUBRID, MS SQL and Vectorwise support the standard syntax. Some also add non-standard SQL extensions.

MySQL: INSERT ... ON DUPLICATE KEY UPDATE

SQLite: INSERT OR REPLACE INTO

PostgreSQL: INSERT INTO ... ON CONFLICT

Answer 2

You could use the EXISTS keyword to check for the existance of rows:

IF EXISTS (SELECT TOP 1 * FROM...)
BEGIN
    UPDATE....
END
ELSE
BEGIN
   INSERT...
END

Answer 3

Just identify the unique item in your data set (like Id or a code). Then by using that try to do a SELECT query first. If the Resultset is empty, do the INSERT else try to UPDATE the details.

Answer 4

you have to first check the data exist in table if exist then use update query otherwise insert data its simple

Answer 5

try to following way:

Example Query

INSERT INTO table (id, name, city) VALUES(1, "ABC", "XYZ") ON DUPLICATE KEY UPDATE
name="ABC", city="XYZ"

for more help see documentation. Click here

Answer 6

package com.stackwork;

//STEP 1. Import required packages
import java.sql.*;
import java.util.Scanner;

public class Updation {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/Employee";

   //  Database credentials
   static final String USER = "root";
   static final String PASS = "admin";
   private static Scanner sc;

   public static void main(String[] args) {
   Connection conn = null;
   Statement stmt = null;
   try{
      //STEP 2: Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");
      //STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);
      //STEP 4: Execute a query
      System.out.println("Creating statement...");
      stmt = conn.createStatement();
      String sql;
      //STEP 5: Get the employee_id for whom data need to be updated/inserted
      sc = new Scanner(System.in);
      System.out.println("Enter the Employee_id for the record to be updated or inserted");
      int Emp_idvalue=sc.nextInt();
      sql = "SELECT * FROM EmployeeDetails where Emp_id="+Emp_idvalue;
      ResultSet rs = stmt.executeQuery(sql);
      if (!rs.next())
      {
          //STEP 6: If the previous details is not there ,then the details will be inserted newly
          System.out.println("Enter the name to be inserted");
          String Emp_namevalue =sc.next();
          System.out.println("Enter the address to be inserted");
          String Emp_addvalue =sc.next();
          System.out.println("Enter the role to be inserted");
          String Emp_rolevalue =sc.next();
          PreparedStatement ps = conn
                    .prepareStatement("insert into EmployeeDetails values(?,?,?,?)");
            ps.setString(2, Emp_namevalue);
            ps.setString(3, Emp_addvalue);
            ps.setString(4, Emp_rolevalue);
            ps.setInt(1, Emp_idvalue);
            ps.executeUpdate();
            System.out.println("Inserted successfully");
      }
      else
      {
        //STEP 7: If the previous details is  there ,then the details will be updated 
          System.out.println("Enter the name to be updated");
          String Emp_namevalue =sc.next();
          System.out.println("Enter the address to be updated");
          String Emp_addvalue =sc.next();
          System.out.println("Enter the role to be updated");
          String Emp_rolevalue =sc.next();
          String updateQuery = "update EmployeeDetails set Emp_id=?,Emp_name=?, Emp_address=?, Emp_role=? where Emp_id='"
                    + Emp_idvalue + "'";
            PreparedStatement ps1 = conn.prepareStatement(updateQuery);
            ps1.setString(2, Emp_namevalue);
            ps1.setString(3, Emp_addvalue);
            ps1.setString(4, Emp_rolevalue);
            ps1.setInt(1, Emp_idvalue);
            ps1.executeUpdate();    
            System.out.println("updated successfully");

      }
      //Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();

   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
  }
}
}

Answer 7

Set any field as the unique identity. For an example consider that employee details has to be entered in the table name **EmployeeDetails.**in this case employee_id can be considered as unique.

use SELECT query select * from EmployeeDetails where employee_id= "the unique keyvalue"; if the resultset is not empty then use UPDATE query to update the fields.

update EmployeeDetails set Employee_id=?,Full_name=?, Designation=?, Email_id=?, Password=? where Employee_id='" + id + "'"; If the resultset is empty then use the INSERT query to insert the values to the table

Insert into EmployeeDetails values(...)