SQL problem if tuple is already in the database
Hello everyone, I am having a problem that I could not resolve. When I insert tuples in a table and call test, I am having the problems. But I do not want to insert a tuple since because it is already in the test database. I also could not use distinct in SELECT because I have to check each tuple. The DECLARE section is declared and so not to worry.
Code:
BEGIN
Open c1;
FETCH c1 INTO read_nameusu, read_namepat, read_prename, read_dtenai, read_depcol, read_worddep;
co_nameusu := read_nameusu;
co_namepat := read_namepat;
co_prenname := read_prename;
co_dtenai := read_dtenai;
co_depcol := read_depcol;
co_worddep := read_worddep;
LOOP
FETCH c1 INTO read_nameusu, read_namepat, read_prename, read_dtenai, read_depcol, read_worddep;
IF read_namepat = co_namepat AND read_prename = co_prename AND read_dtenai = co_dtenai THEN
IF read_depcol IS NULL AND co_depcol IS NULL THEN
INSERT INTO ANOMALY VALUES
(
read_nameusu, read_namepat, read_prename, read_dtenai, read_depcol, read_worddep
);
INSERT INTO ANOMALY VALUES
(
co_nameusu, co_namepat, co_prename, co_dtenai, co_depcol, co_worddep
);
END IF;
IF read_depcol IS NULL THEN
ins_nameusu := read_nameusu;
ins_namepat := read_namepat;
ins_prename := read_prename;
ins_dtenai := read_dtenai;
ins_depcol := read_depcol;
ins_worddep := read_worddep;
ELSE
IF read_depcol > co_depcol AND co_depcol IS NOT NULL THEN
ins_nameusu := read_nameusu;
ins_namepat := read_namepat;
ins_prename := read_prename;
ins_dtenai := read_dtenai;
ins_depcol := read_depcol;
ins_worddep := read_worddep;
END IF;
END IF;
ELSE
IF /* If not in table */ THEN
INSERT INTO ATime VALUES
(
ins_nameusu, ins_namepat, ins_prename, ins_dtenai, ins_depcol, ins_worddep
);
END IF;
END IF;
co_nameusu := read_nameusu;
co_namepat := read_namepat;
co_prename := read_prename;
co_dtenai := read_dtenai;
co_depcol := read_depcol;
co_worddep := read_worddep;
EXIT WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END;
Re: SQL problem if tuple is already in the database
First of all put your primary key or unique index on your key and manage it in a block apart on the right error code.
Secondly, for the rest part I simply do not understand what you are trying to achieve and where exactly are you having/facing the problems. Can you explain me in a simpler manner about what you are trying to do and where exactly you got blocked.
Re: SQL problem if tuple is already in the database
In fact I must be careful to code the lines. Among all the duplicates I want to find a mistake, if depcol is null (date field) then I save the fields of tuple read, and in the next iteration I look when compared to tuple before the person is the same, if so then I look at the date, if still null I save both, if the field is filled depcol then I compare with the old tuple saved. And I take the most recent two and so on.
Re: SQL problem if tuple is already in the database
There is no 30000 solutions:
- to be a constraint and wait that results in a block of exception
- either make an insert or merge if you can afford an update
- be tested if a similar record exists before you insert
Re: SQL problem if tuple is already in the database
select variable := count(id)
from thetableinquestion
where ...
you look at the variable value, if it is > 0 you have already inserted a value ...
In transactSQL, the "if" statements exists, I never know if it exists also in Oracle or not