SQL delete command

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
LVL 7
ayha1999Asked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
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
 
derekkrommCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Ephraim WangoyaCommented:

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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.