--Step 1: create database
CREATE DATABASE Test

USE Test
CREATE TABLE Table1
(
table1ID INT Identity primary key
, data1 CHAR(1)
)

CREATE TABLE Table2
(
table2ID INT Identity primary key
, data2 CHAR(1)
)

--Step 2: Insert some identical values
INSERT INTO Table1(data1)
SELECT 'A'
INSERT INTO Table1(data1)
SELECT 'B'
INSERT INTO Table1(data1)
SELECT 'C'
INSERT INTO Table1(data1)
SELECT 'D'
INSERT INTO Table1(data1)
SELECT 'A'
INSERT INTO Table1(data1)
SELECT 'B'
INSERT INTO Table1(data1)
SELECT 'B'

INSERT INTO Table2(data2)
SELECT 'B'
INSERT INTO Table2(data2)
SELECT 'C'
INSERT INTO Table2(data2)
SELECT 'D'

SELECT * FROM Table1
SELECT * FROM Table2

--Step 3: Remove all the records from Table1 that do not exist in table2
DELETE FROM Table1 
WHERE Data1 NOT IN (SELECT Data2 FROM Table2)

SELECT * FROM Table1
SELECT * FROM Table2

--Step 4: Iterate through table1 removing duplicates until only 1 instance remains
DECLARE @count INT
SET @count = 1

WHILE @count > 0
BEGIN
	DELETE Table1
	FROM Table1
	INNER JOIN 
	(SELECT MAX(table1ID)AS Table1ID FROM table1
	INNER JOIN
		(SELECT Data1 FROM Table1
		GROUP BY Data1
		HAVING COUNT(*) > 1) AS T1
		ON Table1.Data1 = T1.Data1)AS T2
	ON Table1.Table1ID = T2.Table1ID

	SET @count = (SELECT @@ROWCOUNT)
END

SELECT * FROM Table1
SELECT * FROM Table2