limit a delete query 1

[avihaimar]

Hey,
i have table B with FK to A (many to one)

I want to delete big amount of data from A,B (all the data that already read from the table and flaged as readed)

since i have the FK i try to delete data from B before delete A

i run this query
delete from B using B,A where Query_events.flag='yes' and B.A_B_Hjid = A.hjid

and it is ok . the problem that i want to limit it to 10000 records each execute

so i added
delete from B using B,A where Query_events.flag='yes' and B.A_B_Hjid = A.hjid limit 10000

but i have problem with the limit.

any idea pleaaaaaaaaaaaaaaaaaaaase

 

[TonyGroves]

What is the problem - an error message, wrong results, or what?

It's OK, I think I know what the problem is. LIMIT is only available with a simple delete.

I find it hard to understand your query (where does query_events come from?), but what you could probably do (assuming you're using MySQL 4.1 or later) would be something like:
[tt]
DELETE FROM b
WHERE
EXISTS
(
SELECT *
FROM a
WHERE a.flag='yes' AND b.a_b_hjid=a.hjid
)
LIMIT 10000
[/tt]

 

[avihaimar]

Thnak you.

My mistake in the query. Query_Events is -A
should be:
delete from B using B,A where A.flag='yes' and B.A_B_Hjid = A.hjid limit 10000

Any way.

the performance is very important (need to delete around more than 400000 records from B and 200000 from A every hour)

So ,
should i use " exists " or " in " in the query?

in the query that you suggest is it will be better to change the select section to - select 0 or select b.hjid?

Thank you very much.

 

[TonyGroves]

As you suggest, you could use "SELECT 0" instead of "SELECT *"; it would be faster.

I don't understand what you mean by using "exists" or "in". The query shown should work (with MySQL 4.1 or later)

 

[avihaimar]

I mean using -

DELETE FROM b
WHERE
IN
(
SELECT *
FROM a
WHERE a.flag='yes' AND b.a_b_hjid=a.hjid
)
LIMIT 10000

 

[TonyGroves]

That's not valid syntax.