0

I want to insert a one or more records in table A. But same record already existing in records with status Active

CREATE PROCEDURE Test
AS

BEGIN
INSERT INTO A (x,y,z,status)
SELECT data FROM A WHERE some condtion;

... Here i want to update table A status column into 'N', if the inserted data already existing in table A Compared with x,y,z column.

EXCETION
 ---Handled
END;
Nvr
  • 171
  • 1
  • 11

3 Answers3

1

What you probably want to do is a merge-statement. The merge statement checks if the dataset already exists. In this case you can formulate an update statement. For example, you could update the status. If not, the new dataset will be inserted.

For further information you should hava a look here.

wiko
  • 35
  • 5
1

You can try this:

MERGE INTO target_table t
    USING source_table s
    ON (t.id = s.id)
  WHEN MATCHED THEN
    UPDATE SET t.col1 = s.col1
  WHEN NOT MATCHED THEN
    INSERT (col1, col2) VALUES (s.col1, s.col2);

Documentation Link: https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606

Soumendra Mishra
  • 3,483
  • 1
  • 12
  • 38
1

The merge is the way to go. Another approach is first updating all the rows that match the criteria and after do the insert.

However, if your problem is how to differentiate the 2 rows, use the pseudo-column ROWID.

robinsonlovatto
  • 33
  • 3