Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Red face Unanswered: Multiple fields in where statement

    In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record. Below was my first attempt, which of course doesn't work.

    Delete from oop_test where acct_no, curr_seq, acct_type in (select acct_no, curr_seq, acct_type from oop_temp)

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    What about this?

    Delete from oop_test
    where exists(select 'ok' from oop_temp i where i.acct_no=oop_test.acct_no and ....)

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    YES! THAT IS IT! Thanks a TON! I am still a bit confused about the use of the variable "i" in the query. Explain it's use to me. Maybe it is my lack of SQL skills.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This can also be done with a simple join:

    Delete oop_test
    from oop_test
    inner join oop_temp
    on oop_test.acct_no = oop_test.acct_no
    and oop_test.curr_seq = oop_test.curr_seq
    and oop_test.acct_type = oop_test.acct_type

    blindman

  5. #5
    Join Date
    Oct 2003
    Posts
    5
    I will have to give this a shot. While the first method works like a champ, I had a difficult time figuring it out. This is real straight forward.

    THANKS!

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The "i" in Snail's code is just an alias for the table name oop_temp. Many coders assign shorter alias name for tables in their queries to reduce the amount of typing required. Otherwise, Snail's code just checks each record in oop_test to see is a matching record "exists" in the oop_temp based on the three columns specified.

    Both methods work. I think the join method might be more efficient, though for small-to-midsize tables probably not enough to be noticable. Go with whichever method you find easiest to read.

    blindman

  7. #7
    Join Date
    Oct 2003
    Posts
    5
    Actually, the part I had a problem with was the select 'ok' part. Do you know what that means?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

    How it works is like this...

    The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

    where exists(select * from oop_temp...

    ...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

    blindman

  9. #9
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by blindman
    All right, the 'OK" part is odd! Snail, what was your reasoning for the hard-coded text?

    How it works is like this...

    The inner query just needs to see whether a corresponding record exists. It doesn't really need to know what any of the record's values are. Therefore, Snail supplied a hard-coded string 'Ok', which will be returned for every matching record. If 'Ok' exists in the recordset, then there was a matching record. Using:

    where exists(select * from oop_temp...

    ...accomplishes the same thing. The optimizer is smart enough to know that you only want to check for the existence of the record, and won't try to access all the columns in the record.

    blindman
    I agree - there is no difference in performance between select 'OK' and select * inside exists, but 'OK' looks much better for me

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So SSchuler, its just a matter of style, and lookin' good!

    blindman

  11. #11
    Join Date
    Oct 2003
    Posts
    5
    HEHEH! Good one. Ya learn something new everyday. I can't believe that tripped me up like it did. Now that you point it out, it is quite obvious. Don't let anyone say that us computer jockeys don't have style! ;-)

  12. #12
    Join Date
    Sep 2003
    Posts
    522
    * also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by ms_sql_dba
    * also has to be expanded by the optimizer into the field list, while 'ok' doesn't. i usually use if exists (select 1 from table_name)
    I believe that's changed...Where SELECT * is actually optimized to perform better.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett is correct. Select * is optimized, and the columns are not expanded.

    blindman

  15. #15
    Join Date
    Oct 2003
    Posts
    706

    Re: Multiple fields in where statement

    Originally posted by SSchuler
    In need to write a delete that checks to see if a record exists in which 3 specific fields match the same 3 fields in another table. If there is a match it deletes that record.
    To find "a record in which three specific fields match the same three fields in another table," you would use a JOIN clause in a SELECT query.

    To delete those fields, you use this select query as a sub-select in a DELETE query, something like this:

    Code:
    DELETE FROM victim WHERE victim_id IN
    (SELECT id FROM 
    table1 A JOIN table2 B USING
      A.F1 = B.F1 AND A.F2 = B.F2 AND A.F3 = B.F3 
    )
    (Sub-select italicized for emphasis. Caution: extemporaneous SQL coding... do not try this at home.) ;-)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

Posting Permissions

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