Writing delete query to delete records from Table1

[aman]

Hi Guys

I have two tables Table1,Table2. I want to delete records from Table1 whose ID= CoventionalID field value from Table2.

Table1 has two fields (ID,PolicyNumber)

Table2 has 5 fields(ID,PolicyNumber,ConventionalID,ConventionalPolNo)

The following query doesn't delete anything. Any help will be much appreciated.

DELETE *
FROM Table1 WHERE ID=(Select ConventionalID from Table2);

Thanks

 

[Minty]

You have no criteria in the second select...

(Select ConventionalID from Table2[COLOR="Red"] Where ????[/COLOR])

Or you should have a Join.

Simply design the select query to get the records you need then change it to a delete query. That will give you the correct SQL statement.

 

[aman]

Minty, I need to delete all the records from Table 1 whose ID field values with ConventionalID field values of Table2.

There are 191 records in Table2. But in Table1 , there are 300,000 records so I need to match ConventionID of Table2 with ID field of Table 1 and then delete the records..

Hope that makes sense.

 

[Minty]

It does make sense, which is why you need the Join.

Do what I suggested, make a normal query with both tables and the join and make sure it is only selecting the records you need to delete. You will then be able to see the syntax of the SQL to select the records

Then change the query type to Delete .