Update record if exists, otherwise insert a new row

[dbalearner]

I would like to write a simple code to check if a record exists. If so update and set a column. Otherwise insert a new row. I can run the code below. However, I am sure there is a simpler way doing it rather than test for exists via a cursor.

DECLARE
CURSOR c IS
SELECT 1 from next_rowid WHERE nxr_name = 'T1';
l_int integer;
BEGIN
SET TRANSACTION READ WRITE;
OPEN c;
FETCH c INTO l_int;
IF c%found THEN
DBMS_OUTPUT.PUT_LINE(chr(10) ||'Records for T1 exists, updating the record in NEXT_ROWID table' || chr(10));
UPDATE next_rowid SET nxr_next_rowid = 50000000 WHERE nxr_name = 'T1';
ELSE
DBMS_OUTPUT.PUT_LINE(chr(10) ||'Inserting new record for T1 in NEXT_ROWID table' || chr(10));
INSERT INTO next_rowid values ((SELECT max(nxr_id)+1 FROM next_rowid),'T1',50000000);
END IF;
COMMIT;
CLOSE c;
EXCEPTION
WHEN OTHERS THEN
IF c%isopen THEN
close c;
RAISE;
END IF;
END;


Any suggestions?

Thanks

 

[stefanhei]

Hi,

have a look at the merge command and sequences.
The merge command does what your code does and sequences are used to generate numbers. Your statement could look like this:

MERGE INTO  next_rowid n
 USING next_rowid i
 ON (i.nxr_name = 'T1')
 WHEN MATCHED THEN UPDATE next_rowid SET nxr_next_rowid = 50000000 WHERE nxr_name = 'T1'
 WHEN NOT MATCHED THEN INSERT INTO next_rowid values (your_sequence.nextval,'T1',50000000)

Stefan

 

[dbalearner]

Thanks Stefan, I don't use sequence but I will try the code.

 

[dbalearner]

I am getting this error message


MERGE INTO next_rowid n
USING next_rowid i
ON (i.nxr_name = 'T1')
WHEN MATCHED THEN UPDATE SET nxr_next_rowid = 50000000 WHERE nxr_name = 'T1'
WHEN NOT MATCHED THEN INSERT values ((SELECT max(nxr_id)+1 FROM next_rowid),'T1',50000000)


MERGE INTO next_rowid n
*
ERROR at line 1:
ORA-00001: unique constraint (SOPHIS.PK_NEXT_ROWID) violated



There is a primary key on nxr_id. But I was not getting this message with the old code.

Thanks

 

[dbalearner]

I disabled the PK constraint but the code below inserts two rows rather than one when there is no row for nxr_name = 'T1'!

1 MERGE INTO next_rowid n
2 USING next_rowid i
3 ON (n.nxr_id = i.nxr_id and i.nxr_name = 'T1')
4 WHEN MATCHED THEN UPDATE SET n.nxr_next_rowid = 50000000 WHERE n.nxr_name = 'T1'
5* WHEN NOT MATCHED THEN INSERT values ((SELECT MAX(nxr_id) + 1 from next_rowid),'T1',50000000)


2 rows merged.

So I get two duplicate records for T1!

 

[nassrin5]

I am not sure you can merge the same table to itself. With PK constraint the first record goes through and the second record is rejected. So the whole transaction is aborted.

 

[stefanhei]

Sorry, never tried merge with only one table. But this should work:

MERGE INTO next_rowid n
 USING (SELECT 'T1' nxr_name FROM dual) i
 ON (i.nxr_name = n.nrx_name)
 WHEN MATCHED THEN UPDATE SET nxr_next_rowid=50000000 WHERE nxr_name='T1'
 WHEN NOT MATCHED THEN INSERT VALUES (SELECT MAX(nxr_id)+1,'T1',50000000 FROM next_rowid)

Btw. is there a reason why you do not use sequences? With the select max method you might run into problems in a multiuser environment, and performance would increase dramatically for this statment in case next_rowid would be a huge table.

Stefan

 

[dbalearner]

Thanks Stefan. Still some problem

1 MERGE INTO next_rowid n
2 USING (SELECT 'T1' nxr_name FROM dual) i
3 ON (i.nxr_name = n.nrx_name)
4 WHEN MATCHED THEN UPDATE
5 SET nxr_next_rowid=50000000 WHERE nxr_name='T1'
6 WHEN NOT MATCHED THEN
7 INSERT (nxr_id,nxr_name,nxr_next_rowid)
8* VALUES ((SELECT MAX(nxr_id)+1 FROM next_rowid),'T1',50000000)
/
ON (i.nxr_name = n.nrx_name)
*
ERROR at line 3:
ORA-00904: "N"."NRX_NAME": invalid identifier


Actually it will be very easy to test this yourself if you have a chance please


create table next_rowid (
nxr_id number not null ,
nxr_name varchar2(30) not null ,
nxr_next_rowid number(37) not null,
CONSTRAINT PK_next_rowid
PRIMARY KEY ( nxr_id )
);
INSERT INTO next_rowid VALUES ((SELECT MAX(nxr_id)+1 FROM next_rowid), 'T1',50000000)

1 row created.