Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Bangalore
    Posts
    8

    Unanswered: SQL Query to Check for the existance and then insert a record.

    Hi,

    I want to check first if a particular record exists in the database table. If it doesn't exist, then insert the record, else, do not insert.

    How do i accomplish this .. ?

    Plz. suggest.


    Thanks,
    - Jay.

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: SQL Query to Check for the existance and then insert a record.

    Originally posted by Jayathirtha
    Hi,

    I want to check first if a particular record exists in the database table. If it doesn't exist, then insert the record, else, do not insert.

    How do i accomplish this .. ?

    Plz. suggest.


    Thanks,
    - Jay.
    http://tahiti.oracle.com - find PL/SQL documentation

  3. #3
    Join Date
    Sep 2003
    Posts
    156

    Re: SQL Query to Check for the existance and then insert a record.

    Originally posted by Jayathirtha
    Hi,

    I want to check first if a particular record exists in the database table. If it doesn't exist, then insert the record, else, do not insert.

    How do i accomplish this .. ?

    Plz. suggest.


    Thanks,
    - Jay.
    there are several ways of doing this... here is a simple way
    declare

    v_num number;

    begin

    select count(col_name)
    into v_num
    from table_name
    and condition;

    if v_num = 0 then
    insert into table_name values (val1);
    commit;
    else
    raise_exception;
    end if;
    end;
    /
    Last edited by GhostMan; 02-26-04 at 09:01.
    rgs,

    Ghostman

  4. #4
    Join Date
    Jan 2004
    Location
    Bangalore
    Posts
    8
    Sorry, i didn't give the whole scenario.

    Actually, I want to test this through Java programming(JDBC). And I do not want to call any Stored Procedures/functions from the Java Program.

    It is possible to do this in two steps as u said above (using v_num...).
    But i was looking for a solution consisting of a single SQL query to accomplish this.

    Pls. Suggest.


    Thank you so much,
    - Jay.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: SQL Query to Check for the existance and then insert a record.

    If your table has a primary/unique constraint (other than a surrogate key) - and it should - then the easiest way is to insert it regardless and catch the error:
    PHP Code:
    begin
      insert into t 
    ( ... ) values ( ... );
    exception
      when dup_val_on_index then null
    ;
    end

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Jayathirtha
    Actually, I want to test this through Java programming(JDBC). And I do not want to call any Stored Procedures/functions from the Java Program.
    That's a pity. It really would be better to put your database logic in packages and call them from your application.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •