Solved

Delete Rows From Database

Posted on 2009-04-09
4
190 Views
Last Modified: 2012-05-06
Hello,
I have two tables (Employees and Records) that both contain a FirstName and LastName column.  I want to create a Delete Query that will delete every row in the Employee's table where a data row's FirstName and LastName is not found in the Records table.
0
Comment
Question by:Gunit2507
  • 2
4 Comments
 
LVL 15

Expert Comment

by:spprivate
ID: 24108149
delete from employees where firstname+lastname not in
(select firstname+lastname from records).
But I would say this is a bad design and rather I would have Emp ID as a key in the child table and delete by the id.
0
 

Author Comment

by:Gunit2507
ID: 24108258
The other database doesn't have an employee ID or any value like that... So how would that look in SQL?
0
 
LVL 15

Expert Comment

by:spprivate
ID: 24108465
you can use the same query as i mentioned earlier
0
 
LVL 11

Accepted Solution

by:
anyoneis earned 500 total points
ID: 24178342
Just a minor addition: it is possible that simply concatenating first and last names could result in a false match. To ensure this doesn't happen, connect the two wih a delimiter, as in:

delete from employees where firstname + '|' + lastname not in
(select firstname + '|' + lastname from records).


David

0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question