Delete from two tables in SQL Server

Question

I have two tables with lots of IP addresses and I want to delete the identical ones from two tables. I tried twice but none worked for me.

The first query I tried is :

Delete FROM table1, table2 
WHERE table1.IpAdd != table2.Ipaddress

The second query is:

DELETE table1, table2  
FROM table1 
INNER JOIN table2 
WHERE table1.IpAdd = table2.Ipaddress

score 1 · Answer 1 · answered May 03 '15 at 04:24

Your sql statements conflicts with eachother. However, you stated in your text you want to delete the records where the ip adresses match. In any case sql server does not support deleting from 2 tables like you tried.
The easy way is to insert the ip adresses into a temporary table using a select with the join from your second attempt and delete the records from each table joining it to the temporary one.

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

Why not bind them using a foreign key from Table 1 to table 2. So, if there is delete in primary key it automatically removes similar entries in other table as well.

To create a foreign key study this: How do I create a foreign key in SQL Server?

Image to foreign key

edited May 23 '17 at 11:43

Community

1
1

answered May 03 '15 at 04:15

Abhishek Dey

1,601
1
15
38

I want to get rid of both similar data in two tables. – muteb May 03 '15 at 04:17
Yes, so when you delete from table 1 say an entry like `192.168.0.10` it automatically deletes the same row from Table 2. – Abhishek Dey May 03 '15 at 04:18
I have added an image as well. – Abhishek Dey May 03 '15 at 04:20
For that you need at least one of the tables to have the ip colomn as it's primary key. – Zohar Peled May 03 '15 at 04:26

score 0 · Answer 3 · answered May 03 '15 at 05:11

If you want to delete duplicate rows in two tables use this script:

DECLARE @table Table([Ip] varchar(20))

SELECT t1.IpAdd
INTO @table
FROM table1 t1 INNER JOIN table2 t2 On t1.IpAdd = t2.Ipaddress

DELETE FROM table1
WHERE table1.IpAdd In (SELECT [Ip] FROM @table)

DELETE FROM table2
WHERE table2.Ipaddress In (SELECT [Ip] FROM @table)

Delete from two tables in SQL Server

3 Answers3