CODEWITHSUNDEEP
sqldatabasestored-proceduresplsql
user1813228
user1813228

Reputation:

Stored Procedure for UPSERT functionality

Below is my table in which ID column is Primary Key. And other two columns are string.

I am using a new Database named XpressMP.

Column Name                
-------
ID             PrimaryKey                 
SEARCHES       String
ACCOUNT        String

I am trying to achieve functionality of UPSERT here-

If ID doesn't exists here then insert a new record.
And ID exists then update the record.

I know If I am working with Oracle then I can use MERGE sql command but MERGE is not supported in that database and there is no other command for that as of now. But I believe I can do the same thing with Stored Procedure.

Can anyone provide some suggestions how can I do the same thing with Stored Procedure? As stored procedure will work there.

UPDATED:-

public final static String INSERT = "BEGIN"
+" INSERT INTO TABLE (ID, SEARCHES, ACCOUNT) VALUES (?, ?, ?)"
+" EXCEPTION"
+" WHEN DUP_VAL_ON_INDEX THEN"
+" UPDATE TABLE"
+" SET SEARCHES = ?, ACCOUNT = ?"
+" WHERE ID = ?"
+" END";

Whenever I try to execute the above stored procedure like this

preparedStatement = dbConnection.prepareStatement(INSERT);

preparedStatement.setString(1, String.valueOf(userId));
preparedStatement.setString(2, Constants.getaAccount(userId));
preparedStatement.setString(3, Constants.getaAdvertising(userId));
preparedStatement.executeUpdate();

I get exception? Is this the right way to execute it?

Upvotes: 3

Views: 6713

Answers (3)

haki
haki

Reputation: 9759

i agree with @tabish here. you'll have to do it the traditional way. the way you suggested i think your causing more harm - you perform an insert for each row in the source table (to either the target table or to the ERR table) and then performing an additional update. in the worst case, when you have to update all the rows, you would perform twice as much dml operations. this is not a good idea in my book. Good luck.

Upvotes: 2

Orangecrush
Orangecrush

Reputation: 1990

You can handle it using the DUP_VAL_ON_INDEX exception.
You can add the below code in your procedure, which should meet your requirement.

CREATE OR REPLACE PROCEDURE TABLE_UPSERT (v_id IN NUMBER,
                                          v_searches IN VARCHAR2(20),
                                          v_account IN VARCHAR2(20)) AS
BEGIN
  INSERT INTO table (id, searches, account) VALUES (v_id, v_searches, v_account) ;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE TABLE
    SET searches = v_searches, account = v_account
    WHERE id = v_id;
END;
/

You can read more about DUP_VAL_ON_INDEX Exception here and here.

Another option would be to insert or update the data after checking the count.

DECLARE
  l_count number;
BEGIN
  SELECT count(*) 
  INTO l_count
  FROM table 
  WHERE id = (value);

  IF l_count = 0 THEN
    INSERT into table VALUES ....;
  ELSE
    UPDATE table SET searches = .., account = ..;
  END IF;
END;

Upvotes: 1

Tabish Sarwar
Tabish Sarwar

Reputation: 1525

Well I think you have to do it in a traditional way.

IF ROW DOESN'T EXIST INSERT ELSE UPDATE

Upvotes: 1

Related Questions