Deleting records in MS SQL

I have the following SQL query:

SELECT T1.*

FROM Table1 T1, Table2 T2

WHERE T1.Key1 = T2.Key1

AND T1.Key2 = T2.Key2

AND T2.DateField = '2007-May-10'

Can someone tell me how to modify this query to delete the records returned by it? SQL does not permit me to use 'DELETE T1.*'

#455835

You'll have split and use two SQL Queries for deleting....

Query1:

DELETE

FROM Table1

WHERE key1 = '2007-May-10'

Query2:

DELETE

FROM Table2

WHERE key2 = '2007-May-10'

#455850

OK, I didn't explain adequately.

Let's take this example. Table1 has 300 records. Table 2 has 30 records which match with 30 records in Table1.

Now what I am attempting to do is delete those 30 records from Table1. The query I provided earlier is used for matching the records between both tables.

Please note that this is only an example and in reality, I have millions of records in both the tables (it is an ERP). Therefore I need a robust query capable of deleting hundreds of thousands of lines.

Any ideas?

#455854

I believe then your first query is correct in theory, if you want to just delete records from single table using (only reference from) second table

Just need little change :

DELETE 
FROM table1
WHERE table1.key = table2.key
AND 
table2.key = '2007-May-10'

I believe this should work for you !!

#455855

no no ...... make sure you are defining the datefield and not key, otherwise the last post's code will owrk but it should read

DELETE 
FROM table1
WHERE table1.key = table2.key
AND 
table2.DateField = '2007-May-10'

#455910

I didn't mean the query to use as it as Lanboy... although I say you're right. The query that I wrote is for structure reference only.

#455927