How to write SQL statement with dynamic where clause?

How do you write a sql statement for a 'where in' clause against a composite key?

• Using MSSQL 2005 I am used to writing a statement like this: delete from myTable where ID in (select ID from otherTable where deleted = 1) How can I do this when otherTable has a composite primary key? The composite key has two columns: docnum float version int (My google-fu suggests using CTEs to do this however I have no experience with them.)

• Answer:

  In MS SQL you can do this: DELETE T FROM myTable T INNER JOIN otherTable OT ON T.docnum = OT.docnum And T.version = OT.version There's a similar syntax for updates too.