Paste the data and, if it is already inserted, update in sql

Question

I just want to insert data into the SQL database table and if some data has already been inserted, I want to update this data. How to do it using Java. Please help me and sorry for the bad English in advance.

Answer 1

Just specify a unique element in your dataset (e.g. Id or code ). Then, using this, try executing the SELECT query first. If the Resultset parameter is empty, add INSERT to UPDATE .

Answer 2

You can use the EXISTS keyword to check for rows:

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

Answer 3

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

Since you did not specify which dialect of the DBMS you are asking, I refer you to the Wikipedia article “ Merge (SQL), ” which covers most dialects of the DBMS. Short description:

 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 standard syntax. Some also add custom SQL extensions.

MySQL: INSERT ... ON DUPLICATE KEY UPDATE

SQLite: INSERT OR REPLACE INTO

PostgreSQL: INSERT INTO ... ON CONFLICT

Answer 4

you need to first check that the data exists in the table, if exist, then use the update request, otherwise insert the data its simple

Answer 5

try the following way:

Request example

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

see the documentation for more information. Click here

Answer 6

Set any field as a unique identifier. As an example, consider that employee data must be entered in the table name ** EmployeeDetails. ** In this case, employee_id can be considered unique.

use the SELECT query . select * from EmployeeDetails, where employee_id = "unique key value"; if the result set is not empty, use the UPDATE query to update the fields.

update EmployeeDetails set Employee_id = ?, Full_name = ?, Obvalue = ?, Email_id = ?, Password =? where Employee_id = '"+ id +"' "; If the result set is empty, use the INSERT query to insert values ​​into the table

Insert into values ​​EmployeeDetails (...)

Answer 7

 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(); } } }