如何在单个查询中更新多行数据？

I have multiple rows in database.

我在数据库中有多行。

I need to collect all the rows based on certain condition and change the value of particular column by removing a word from the column's value.

我需要根据特定条件收集所有行，并通过从列的值中删除一个单词来更改特定列的值。

How it could be done with with CakePHP 3?

如何使用CakePHP 3完成？

2 个解决方案

#1

Try the following:

请尝试以下方法：

$model->updateAll(
        ['description' => "REPLACE (description, 's', 'a')"], // fields
        ['is_disabled' => 1] //condition
);

This will generate the following sql:

这将生成以下sql：

UPDATE 
    mytable
SET 
    description =  REPLACE (description, 's', 'a') 
WHERE
     is_disabled = 1

Note that this is replacing the matching string ('s') everywhere where it appears in the description field - even in the middle of a word. This often leads to wellknown clbuttic mistakes.

请注意，这将替换匹配字符串（'s'），它在描述字段中出现的位置 - 即使在单词的中间。这通常会导致众所周知的clbuttic错误。

#2

Add this on top of the controller "use Cake\Datasource\ConnectionManager;"

在控制器顶部添加“使用Cake \ Datasource \ ConnectionManager;”

$this->loadModel('Rewards');
$connection = ConnectionManager::get('default');
$results = $connection->execute("UPDATE rewards SET description =  REPLACE (description, 's', 'a') where is_disabled=1");

I have values in description fields 'wwss' now that fields word replace into 'wwaa' like that.

我在描述字段'wwss'中有值，现在字段替换成'wwaa'就像那样。

For more explanation refer this link

How can I use mySQL replace() to replace strings in multiple records?

如何使用mySQL replace（）替换多个记录中的字符串？

Please review,Thanks!

请检讨，谢谢！

#1