Deleting records

[Pauldohert]

I have a query (Query1) which returns results In one field recordID.

How do I delete records in a table where the RecordID is in the list Query1.recordID?

Thanks

 

[RV]

I have a query (Query1) which returns results In one field recordID.

How do I delete records in a table where the RecordID is in the list Query1.recordID?

Thanks

DELETE
FROM table
WHERE RecordID IN
(
SELECT RecordID
FROM Query1
)

or

DELETE
FROM table
WHERE EXISTS
(
SELECT *
FROM Query1
WHERE Query1.RecordID = table.RecordID
)

RV

 

[Pauldohert]

Thanks RV - I have tried that, Even with a table of only 50 records this takes 10s of seconds.

When the data is live - there will be 10s of thousands of records.

 

[RV]

Thanks RV - I have tried that, Even with a table of only 50 records this takes 10s of seconds.

No ideaa why it takes so long.
It's unlikely that it's due to the DELETE statement.
Setting up an index against RecordID could help.

Are you using a separate backend?
If so, Access or a database (SQL Server, ORACLE, ...)

RV

 

[Pauldohert]

I am using a SQL server BE.

Does the query need to run The IN
(
SELECT RecordID
FROM Query1


for each line in the table - which may explain why it takes so long.


Ie if the Query1 just returned 12 - and I hard coded In(12) it's quick - however if I use In(SELECT RecordID
FROM Query1) its v slow.

RecordID is the PK of the table.

Thanks