How to update multiple row data in a single query?

Question

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?

UPDATE website_content_pages SET content = REPLACE(content, 'b>', 'strong>'); try this query in sql it's do both work it not work in cakephp 3.x — karthik, Oct 14 '15 at 17:19

score 1 · Answer 1 · edited Oct 15 '15 at 09:33

1

Try the following:

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

This will generate the following 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.

edited Oct 15 '15 at 09:33

AD7six

63,116
12
91
123

answered Oct 15 '15 at 06:49

Krunal Dave

272
6
16

`change the value of particular column by removing a word from the column's value.` The updateAll call in this answer doesn't do that, or make it apparent how it could be changed to do that. – AD7six Oct 15 '15 at 08:54
I've amended the answer so that it answers the question - in future it is a better idea to only write an answer, if it does actually answer the question. Thanks for using whitespace in your code examples - please do keep up that habit (and fix your existing questions/answers which don't do this). – AD7six Oct 15 '15 at 09:35
"REPLACE (description, 's', 'a')" totally this values are updated into my columns – karthik Oct 15 '15 at 09:40

score 0 · Accepted Answer · edited May 23 '17 at 12:23

0

Add this on top of the controller "use 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.

For more explanation refer this link

http://book.cakephp.org/3.0/en/orm/database-basics.html

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

Please review,Thanks!

edited May 23 '17 at 12:23

Community

1
1

answered Oct 14 '15 at 18:11

karthik

161
10

1

Why are you answering your own question as if you don't know the person who asked the question? – AD7six Oct 15 '15 at 08:56
@AD7six Hi 1st searched the answer for this question and got the answer from the document it works perfect to me.so i post the answer for my question – karthik Oct 15 '15 at 09:26
Answering your own question is great (and encouraged) adding this when self answering is quite bizarre `I hope this is usefull for your question.Please review,Thanks!` – AD7six Oct 15 '15 at 09:28
@AD7six i am sry. it's my careless :) Now i edit and updated my question – karthik Oct 15 '15 at 09:36

How to update multiple row data in a single query?

2 Answers2