Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Unanswered: value does (not) exist in column x in table y

    How do I say that in a query? If the value "Ed Wood" does not exist in the "name" column of the "person" table, then make a new row and insert him.

    I'm trying to simplify my project so I have a chance of getting it done in time, which is one reason I've lumped the first and last names together. Not beautiful, but I'm running out of time.

    I'm doing this in ColdFusion with some SQL queries. The part I'm choking on should be pretty straightforward SQL syntax, I think. I'm using SQL*Plus on a Mac and a PC alternately.

    Code so far (CFIF is a ColdFusion IF statement):

    <CFIF <!--- if user input (from form field) DIRECTOR_NAME does NOT match an entry in the person table ---> >

    INSERT INTO person (person_id,
    name,
    bio)
    VALUES(personid_seq.nextval,
    '#Trim(FORM.DIRECTOR_NAME)#',
    NULL)
    </CFIF>

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    have a look at the merge statement which is avaiable since 9i which might be to construct you are looking for

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You can't use IF user input doesn't exist ... THEN unles this, written in italic, is a function (preferrably boolean one) which will check whether your director exists in the table or not.

    If you don't want (need, whatever) a function, check his existence in a select statement before an IF. Something like:
    Code:
    declare 
      it_exists varchar2(1);
    begin
      select 'x' into it_exists
      from director d
      where d.director_name = :form.director_name;
    
      /* it exists; do nothing */
    
      exception
        when no_data_found then
          insert into director
            (id, name)
            values
            (seq.nextval, :form.director_name);
    end;

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Or:
    Code:
    begin
      insert into director(id, name)
        values(seq.nextval, :form.director_name);
    exception
      when dup_val_on_index then null;
    end;

  5. #5
    Join Date
    Apr 2004
    Posts
    246
    INSERT INTO person (person_id, name, bio)
    select personid_seq.nextval, '#Trim(FORM.DIRECTOR_NAME)#', NULL
    from dual
    where not exists (
    select null from person
    where name='#Trim(FORM.DIRECTOR_NAME)#'
    )

    one of the things we had come up with in the dark ages before pl/sql
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

Posting Permissions

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