I have a table that contains these two real fields current
and origin
.
current
value is updated regularly. I want to write a script of reset: for each row I want to put the origin
value in the current
value.
In MySQL it's possible with this query:
update MyTable set current = origin
I tried to write this in Yii2 with the query builder:
return $this->updateAll(['current' => 'origin']);
But this doesn't work because origin
is interpreted as string and all rows updated with the value 0
.
So how I can update field value by the value of another field using updateAll()
?
In such a case, you can use the following UPDATE statement syntax to update column from one table, based on value of another table. UPDATE first_table, second_table SET first_table. column1 = second_table. column2 WHERE first_table.id = second_table.
To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.
In MySQL, if you want to update a column with the value derived from some other column of the same table we can do so by using a SELF JOIN query and if you wish to modify the value derived from another column like maybe get a substring from the text or break the string using some delimiter, then we can use the ...
UPDATE syntax:UPDATE table_name SET column_name = value WHERE condition; To perform the above function, we can set the column name to be equal to the data present in the other table, and in the condition of the WHERE clause, we can match the ID. we can use the following command to create a database called geeks.
Wrap origin
in yii\db\Expression like so:
use yii\db\Expression;
...
return $this->updateAll(['current' => new Expression('origin')]);
and result will be as expected.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With