Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Fastest DECODE with EXISTS?

    I have a typical SQL statement:
    Insert into X
    ( field1, field2)
    Select
    (field3, field4)
    from Y

    I need to get an 'Y' into field 1 if another field (FieldX) exists in another table's FIELDZ.

    I typically hard code stuff using DECODE() in the select statement.
    I have also used the EXISTS(Select...) to limit the number of records into a table.

    What is the fastest way to accomplish the lookup and update the field?

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I'm having trouble seeing the connection between DECODE and EXISTS in your example, or what either of them have to do with INSERT.

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    Sorry -
    I utilize decode to "hard code" a value within the insert statement.
    I sometimes utilize Exists() to limit the data inserted at the end of the insert statement.

    What I would like to do is force a value into the table if one of the fields exists in another table.
    I would like to accomplish this within the insert statement.

    The logic would be a combination of Decode(xxx,exists()) within the insert but I know that doesnt make SQL sense.

    Hope that helps out.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Perhaps CASE does what you want:
    Code:
    SQL> create table t1 (x varchar2(1));
    
    Table created.
    
    SQL> insert into t1 (x) values (case when exists (select null from emp) then 'Y' else 'N' end);
    
    1 row created.
    
    SQL> select * from t1;
    
    X
    -
    Y

Posting Permissions

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