How to delete multiple rows?

Question

So I can delete one row using:

$this->db->delete($tableName,array("id"=>4));

...but I can't figure out how to delete multiple rows. I tried:

$this->db->delete($tableName,array("id"=>4,"id"=5));

as well as:

$this->db->delete($tableName,array(array("id"=>4),array("id"=5)));

...but they both don't work. I feel like this should be pretty easy. Any answers?

Joe · Accepted Answer · 2012-05-09 01:40:01Z

40

Have you tried this ?

$names = array(4,5);
$this->db->where_in('id', $names);
$this->db->delete('mytable');

edited May 9, 2012 at 1:40

Joe

2791 gold badge4 silver badges12 bronze badges

answered May 8, 2012 at 6:38

Moyed Ansari

8,4612 gold badges38 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Umar Adil Over a year ago

How can I delete multiple records with delete condition on multiple columns like delete from tbl where col1 = 1 AND col2 = 2; I have this condition in multidim array

Clain Dsilva Over a year ago

great finding ..!! this was not documented in CI docs. Works like a charm

Ivan Ferić · Accepted Answer · 2013-01-31 10:33:24Z

7

not need of associative array.

$ids[] = 1;
$ids[] = 2;

$this->db->where_in( id, $ids );
$this->db->delete('Table_Name');

edited Jan 31, 2013 at 10:33

Ivan Ferić

4,76311 gold badges40 silver badges47 bronze badges

answered Jan 31, 2013 at 10:13

Abuzer Firdousi

1,5561 gold badge12 silver badges25 bronze badges

Comments

Mohit Bumb · Accepted Answer · 2012-05-08 05:55:35Z

4

write custom query for it

$this->db->query("DELETE FROM `TABLE_NAME` WHERE `id` IN(1,2,3,4,5)");

answered May 8, 2012 at 5:55

Mohit Bumb

2,4935 gold badges33 silver badges54 bronze badges

1 Comment

Joe Over a year ago

This looks like it should work as it's the manual version of @MoyedAnsari's answer which uses CI's where_in function. Thanks.

Raja Ram T · Accepted Answer · 2014-05-15 09:54:42Z

3

it is not work

$names = array(4,5);
$this->db->where_in('id', $names);
$this->db->delete('mytable');

DELETE FROM table_name WHERE id IN ('4,5')

notice one thing not single string/digit ('4,5') we should be dived each and every id with single quotation like this ('4','5')

The best and good way

        //$ids = 1,2,3.....
        $ids_exp = explode(',',$ids);
        $this->db->where_in('id',$ids_exp);//
        $this->db->delete('table_name')
        return $this->db->affected_rows();

the above query will be work enjoy..........

answered May 15, 2014 at 9:54

Raja Ram T

8628 silver badges8 bronze badges

1 Comment

Freddy Sidauruk Over a year ago

why return always 1 when using return $this->db->affected_rows();

user684202 · Accepted Answer · 2012-05-08 03:38:07Z

1

To delete a single use row:

$this->db->delete('TABLE_NAME', array('id' => 5));

Have you tried doing this? I'm pretty sure it should work.

$this->db->delete('TABLE_NAME', array(array('id' => 5), array('id' => 3), ...));

answered May 8, 2012 at 3:38

user684202

Comments

anon123 · Accepted Answer · 2020-10-21 12:45:57Z

0

This is an old question, but I'll answer it since I had to research the same question 8 years later.

I checked the source code of $wpdb->delete(), and it will basically only delete 1 row. It works by matching 1 col to 1 val, and then joining with AND. So if you do array("id"=>4,"id"=>5), it will read it as row with (id==4 AND id==5). No rows will match that condition.

So to delete multiple rows, the only way (as far as i've seen) is to use a custom query:

$wpdb->query("DELETE FROM `TABLE_NAME` WHERE `id` IN(1,2,3,4,5)");

answered Oct 21, 2020 at 12:45

anon123

5194 silver badges4 bronze badges

Collectives™ on Stack Overflow

How to delete multiple rows?

6 Answers 6

2 Comments

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related