A little MySQL help...

[camperjohn64]

I have query that gets data from the database, processes that data, then deletes the original data, but I need help with the query to "delete only the data we just SELECTED".

Currently I have this

// Get data using query
SELECT bd_action,bd_target,SUM(bd_count) AS bd_count,bd_data FROM bd_action GROUP BY CONCAT(bd_data,bd_action,bd_target)

// Delete the data (actually just delete everything)
DELETE FROM bd_action

My problem is that when I add a "LIMIT 10000" to the original query, I need to add the same LIMIT to the delete statement

Modified but not working:

// Get data using query LIMIT 10000
SELECT bd_action,bd_target,SUM(bd_count) AS bd_count,bd_data FROM bd_action GROUP BY CONCAT(bd_data,bd_action,bd_target) LIMIT 10000

// ?? Delete the data that I just got (delete ONLY the stuff I just got in the previous query)
DELETE FROM bd_action WHERE (SELECT bd_action,bd_target,SUM(bd_count) AS bd_count,bd_data FROM bd_action GROUP BY CONCAT(bd_data,bd_action,bd_target) LIMIT 10000)

I can't find the right DELETE FROM query that will delete only those items I just queried.

Thoughts?

[Dankasaur]

Set an ID on the data field, then when you loop over that data, at the end send the delete query with WHERE data_id = $query->data_id

Then when it's done with that row, it'll delete it and move on to the next one.

[KillerK]

DELETE FROM bd_action WHERE (SELECT bd_action,bd_target,SUM(bd_count) AS bd_count,bd_data FROM bd_action GROUP BY CONCAT(bd_data,bd_action,bd_target LIMIT 10000) )

I would try that?

Also, copy the table to a new table, and try running it against that one first.

Otherwise, you can do it with php