CODEWITHSUNDEEP
sqloracle-database
MOR_SNOW
MOR_SNOW

Reputation: 831

Check if record exists, and find out which column(s) has changed

I have a procedure that has the following code:

INSERT INTO DON_RECIP_RELATION (NUMBER, ID) 
  SELECT DISTINCT (rec.NUMBER), cur_ID
  FROM REC rec, DON don
  WHERE don.NUMBER = rec.NUMBER
  ...;

This works fine on an insert. But if I try to update an existing record, I get an exception saying: ORA-00001: unique constraint on the primary key, which is the ID.

I figured I have to make a check if the record already exists, then make an update on the specified column. But the table has like 30 columns or more, and while writing the sql code, I don't know which column the user is trying to alter. How can I do this. Say there is a column called NAME, and I know that's the column the user is trying to change, then I can do something like:

IF EXISTS(
   SELECT ID FROM TABLE WHERE ID=cur_ID)
BEGIN
   UPDATE TABLE SET NAME = (SELECT NAME FROM TABLE WHERE ...) WHERE ID=cur_ID;
END

And I need to merge the IF/WHEN EXISTS with the INSERT, but I'm a little lost, as I'm still new to SQL.

PS. The cur_ID is a parameter give to the procedure, and it comes from the user (an aCREATE OR REPLACE PROCEDURE "spUpdateDonRecipRelation"(cur_ID IN NUMBER)

Upvotes: 0

Views: 199

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

First, you can still get duplicates from the select. The distinct does not prevent this. I would suggest:

SELECT MAX(rec.NUMBER), cur_ID
FROM REC rec JOIN
     DON don
     ON don.NUMBER = rec.NUMBER
GROUP BY cur_id;

Then, you can put the check in a WHERE clause:

INSERT INTO DON_RECIP_RELATION (NUMBER, ID) 
    SELECT MAX(rec.NUMBER), don.cur_ID
    FROM REC rec JOIN
         DON don
         ON don.NUMBER = rec.NUMBER
    WHERE NOT EXISTS (SELECT 1 FROM DON_RECIP_RELATION drr WHERE drr.ID= don.cur_ID)
    GROUP BY don.cur_id;

(I am guessing that cur_id comes from the don table.)

Upvotes: 1

Related Questions