Mysql find result_id where name = "x" then delete other entries that share that result_id and name="y"

Question

I'm using a mixture of php and mysql to do the following query and I'm sure there must be a way to do it in just SQL. I've stripped out some of the code and left enough that you should be able to see what I'm trying to accomplish.

Any help would be much appreciated!

SELECT result_id FROM categories
WHERE name = "conventional"


foreach ( $results as $row ){
    $query = "
    DELETE FROM categories
    WHERE result_id = $row->result_id && (name = 'va' OR name = 'fha' OR name = 'usda' OR name = 'other')
    ";
    $this->db($query);
}

EDIT: From the answers I've received so far, I don't think I've explained this well enough. the columns in this table are id, result_id, name

There are multiple entries that share the same result_id as they relate to entries in the "results" table. So I'm trying to find the result_id's of entries with the name "conventional" so I can find all the entries with the same result_id and delete them if their names are "va", "fha", "usda" or "other"

basically, you want to avoid delete query in loop and here is how to do it. See my answer. — Raheel Hasan, Apr 19 '13 at 10:36

Arnelle Balane · Accepted Answer · 2013-04-19T10:36:38.080

3

DELETE FROM categories 
WHERE name IN ("va", "fha", "usda", "other")
AND result_id IN (
  SELECT result_id FROM categories
  WHERE name = 'conventional'
)

edited Apr 19 '13 at 10:36

answered Apr 19 '13 at 10:28

Arnelle Balane

5,437
1
26
32

Thanks a lot for the updated answer, allthough I'm receiving the following error "You can't specify target table 'categories' for update in FROM clause" – Martin Hunt Apr 19 '13 at 12:04
Fixed this using the solution here http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause thanks a lot for the help! – Martin Hunt Apr 19 '13 at 13:20

score 0 · Answer 2 · edited Apr 19 '13 at 10:50

You want to avoid delete query in loop and here is how to do it:

SELECT result_id FROM categories
WHERE name = "conventional"

// Now build csv of ids    
$arx = array();
foreach ( $results as $row ) {
    $arx[] = $row->result_id;
} 

if(!empty($arx)) {
    $arx_1 = implode(',', $arx);
    $query = "DELETE FROM categories
    WHERE result_id in ({$arx_1}) AND (name = 'va' OR name = 'fha' OR name = 'usda' OR name = 'other')";

    $this->db($query);
}

Mysql find result_id where name = "x" then delete other entries that share that result_id and name="y"

2 Answers2