Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Delete Rows From Database

Posted on 2009-04-09
4
191 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!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with duplicate records in Oracle query 16 40
SQL: launch actions one before the other 10 24
SQL Insert parts by customer 12 42
T-SQL: New to using transactions 9 46
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 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