CODEWITHSUNDEEP
oracle-databaseselectplsqlrow
jalal rasooly
jalal rasooly

Reputation: 705

how to find a record from a table with existing record in oracle?

here's a table with a high number of columns

create table test (
id    number,
col_1 varchar2(50),
col_2 varchar2(50),
.
.
col_n varchar2(50)
);

and the table is filled with some example data

insert into test values(1,'a1','a2',...,'an');
insert into test values(2,'a1','a3',...,'am');
insert into test values(3,'a4','a2',...,'ax');

now i need to copy one row (fore example the row with id=1) and change just one column's value, if the result does not be similar to another row (without considering id). some thing like this:

declare
 r test%rowtype;
 var1 number;
begin
 insert into r from test where id = 1;
 r.col_2='a3';
 select count (*) into var1 from test where 'the row without id' = r;
 if (var1 = 0) then
  insert into test values r;
 end if;
end;

but i don't know how to write the select part in oracle. consider table test has lots of columns so you can't write all of the columns in where clause.

Upvotes: 1

Views: 170

Answers (1)

Brian McGinity
Brian McGinity

Reputation: 5945

It is sort of hard to understand what you need. I'll take a shot here. Let me know if this is on track....

To make things easy, can you create a unique index on the table?

    create unique index test_uidx on test (col_1, col_2, ... col_n);

And then let oracle do the work:

    declare
      r     test%rowtype;
      var1  number;
    begin
      select * into r from test where id=1;  --- get the row
      r.col_1  := 'some new value';          --- change 1 value

      begin
         insert into test values r;          --- insert the row
      exception
        when dup_val_on_index then           --- row was a dup
          null;                              --- and did not insert
      end;
   end;

Upvotes: 2

Related Questions