Any ideas how to accomplish this in SQL 1

[AdamVerellen]

Right now I am doing this on an Access Database.
But I am converting the application to SQL.
Just a little bump I hit.

ILVSDB.Execute ("DELETE DISTINCTROW Field1, Field2 FROM Table1 INNER JOIN Table2 ON (table1.Field2 = table2.field2) AND (Table1.field1 = Table2.field1) WHERE (Table1.field1=Table2.field1) AND (table1.field2=table2.field2);&quot

It seems SQL doesn't like to Delete using an inner join
Or use the work Distinct

 

[RJFost]

T-SQL doesn't support DISTINCTROW; if you do a search on the Microsoft KB site, there are quite a few KB/TechNet articles on getting around it.

As for the JOIN, why are you duplicating the functionality in your WHERE clause (in the Access code)?

That aside, take a look at something like this in T-SQL:

DELETE * FROM table1
WHERE field1, field2 IN
(SELECT DISTINCT field1, field2 FROM table2)

That should get the job done.

 

[AdamVerellen]

Didn't exactly work right off.
But figured this way would work.
You think?

DELETE FROM TableA
WHERE EXISTS
(SELECT DISTINCT Field1, Field2
FROM TableB

 

[RJFost]

That, too. Good call; I always forget about EXISTS at the beginning- guess I should get my dosage increased...

 

[AdamVerellen]

Actually ended up with this

DELETE FROM TableA
WHERE NOT EXISTS
(select field1, field2 from TableB
where TableA.field1 = TableB.field1
and TableA.field2 = TableB.field2)

But all on all, definately helped me look in the right direction.
Found this page as help.

http://www.techonthenet.com/sql/index.htm

 

[RJFost]

EXISTS is like the parking brake- I think of it only after I've called a tow truck & said that my car won't move. Used to have a similar issue with HAVING. I think it's from having too many SQL variations running around in my head...