Solved

SQL delete command

Posted on 2011-03-03
4
327 Views
Last Modified: 2012-05-11
How can I check if parts from Table1 exists in Table2 and Table3 before delete record from Table1?

Table1
partno partname


Table2
partno iQty

Table3
partno oQty

How can I write a single sql statment to check the existence of table1's partno in table2 and table2 and if not exists delete record from Table1.

thanks

ayha
0
Comment
Question by:ayha1999
4 Comments
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
delete t1
from table1 t1
left join table2 t2
on t1.partno = t2.partno
left join table3 t3
on t1.partno = t3.partno
where t2.partno is null and t3.partno is null
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
Comment Utility
delete fom table1 t1 wherenot exists (select 1 from (
select partno from table2 union select partno from tale3 ) t on t1.patno = t.partno)
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility

delete from Tbale1
where partno = your number
and not exists (select 1 from Table2 B
                       where B.partno = A.partno)

0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 250 total points
Comment Utility
To delete existing records in Table2 and Table3

delete from Table1
whete Table1.partno in
( Select partno from table2 union Select partno from table3)

To delete not existing records in Table2 and Table3

delete from Table1
whete Table1.partno not in
( Select partno from table2 union Select partno from table3)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now