CODEWITHSUNDEEP
phpmysql
user198989
user198989

Reputation: 4665

MySQL - Delete value in the row, instead of deleting the row

I want to delete the searched value (not row) if a row contains the searched value. For example, if I want to remove banana, it should only remove banana from the rows which contain banana.

Tried this,

DELETE FROM users where eat like '%banana%'

However, it removes the rows. So how can I remove only search value from the rows ?

Upvotes: 0

Views: 87

Answers (4)

Sougata Bose
Sougata Bose

Reputation: 31749

You can try this - 

UPDATE users SET eat = REPLACE(eat, 'banana', '') where eat like '%banana%';

This would replace only banana from eat column where it is present.

Update

Loop through the data and replace those values. This might help - 

$check_val = 'banana';

//select those rows first
"select id, eat from users where eat like '%" . $check_val . "%'"

foreach($data as $v) {

    $temp= explode(',', $v['eat']);
    $temp= array_map(function($t) use($check_val) {
        return (strpos($t, $check_val) !== false) ? null : $t;
    }, $temp);
    $temp = array_filter($temp);
    $v['eat']= implode(',', $temp);

    "update users set eat= '" . $v['eat'] . "' where eat like '%" . $check_val . "%'"
}

Upvotes: 2

Mr. Engineer
Mr. Engineer

Reputation: 3515

Try this query : 

update users set eat='' where eat like '%banana%'

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

 Update users  
 set eat =  'your value' 
 where eat like '%banana%' ;

Upvotes: 1

Sohel Ahmed Mesaniya
Sohel Ahmed Mesaniya

Reputation: 3450

UPDATE users SET eat = null where eat like '%banana%';

OR

UPDATE users SET eat = '' where eat like '%banana%';

Upvotes: 2

Related Questions