How to delete records from some sql tables at one time?

Question

I have two tables:

ruleTBL: ruleid hostname
policyruleTBL: ruleid policyid

In the ruleTBL, the hostname is unique and one hostname may have more than one ruleid. I want to delete all the records in the two tables with one hostname. How should I write the statement?

For example, if these are the records in ruleTBL

 (ruleid = 1, hostname = 'news')
 (ruleid = 2, hostname = 'news')

and in policyruleTBL:

(ruleid = 1, policyid = 4)
(ruleid = 2, policyid = 5)

I want to delete all the records with one statement. Thank you so much!

Print your sample data table. Your explanation is not understandable. — Nithyanandhan M, Jan 12 '15 at 14:02

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

 delete ruleTBL, policyruleTBL from ruleTBL inner join policyruleTBL where ruleTBL.ruleid=policyruleTBL.ruleid and ruleTBL.hostname="name";

Checkout this thread it might lead you to your goal

edited May 23 '17 at 11:50

Community

1
1

answered Jan 12 '15 at 14:01

Yehia Awad

2,898
1
20
31

score 0 · Answer 2 · answered Jan 12 '15 at 14:15

Your question is quite hard to understand.

Anyway, I think you are asking how to delete from multiple tables with a JOIN:

DELETE t1.*, t2.*
FROM t1
JOIN t2 ON t1.rule_id = t2.rule_id
WHERE <some condition>

This will delete all records in both tables that would have been returned if you replaced DELETE with SELECT...

score 0 · Accepted Answer · answered Jan 12 '15 at 14:18

0

You could use multiple table delete statement

DELETE ruleTBL, policyruleTBL 
FROM ruleTBL
JOIN policyruleTBL ON ruleTBL.ruleid = policyruleTBL.ruleid
WHERE ruleTBL.ruleid=1;

answered Jan 12 '15 at 14:18

lakshya_arora

791
5
18

How to delete records from some sql tables at one time?

3 Answers3