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
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.
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?
192.168.0.10 it automatically deletes the same row from Table 2.
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)
