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)What about this?

Delete from oop_test
where exists(select 'ok' from oop_temp i where i.acct_no=oop_test.acct_no and ...)|||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.|||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|||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!|||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|||Actually, the part I had a problem with was the select 'ok' part. Do you know what that means?|||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|||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 ;)|||So SSchuler, its just a matter of style, and lookin' good! :cool:

blindman|||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! ;-)|||* 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)|||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 is correct. Select * is optimized, and the columns are not expanded.

blindman|||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:

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.) ;-)|||A little late, sundial. Read other member's posts first!

blindman