0

I have a query which updates data in 'members' table:

$this->db->set('game_vault_balance', 'CASE WHEN game_vault_balance > 0 THEN game_vault_balance - (@sum_from_vault := 0.27 / 100 * game_vault_balance) ELSE @sum_from_vault := game_vault_balance END', FALSE);
    $this->db->set('available_game_balance', 'available_game_balance + @sum_to_second_table := (@sum_from_vault + ((110 / 100) * rating * @sum_from_vault))', FALSE);
    $this->db->set('game_balance_last_time', 'NOW()', FALSE);
    $this->db->where('game_balance_last_time <= now() - INTERVAL 1 DAY');
    $this->db->limit(1000);
    $this->db->order_by('rating', 'DESC');
    $this->db->update('members');

And I have the second table 'settings':

+------+-----------+------------------+----------+---------------------+
|id    | category  | setting_name     | value    | datetime            |
+----------------------------------------------------------------------+
| 1    | my_cat    | sett_name        | 0        | 2020-08-02 22:29:06 |
+------+-----------+------------------+----------+---------------------+
| 2.   | cat2      | sett_name2       | 1        | 2020-08-02 22:29:06 |
+------+-----------+------------------+----------+---------------------+

As you can see, this query have @sum_to_second_table variable. Right after (or may be during) updating data in 'members' table I need to update 'sett_name' value' of 'settings' table with 'value + @sum_to_second_table' and also a new date. So 'value' will be the sum of @sum_to_second_table of all rows from query to 'members' table.

I think that JOIN don't work here. Any ideas?

UPDATE

I want to make something like that:

$this->db->set('members.game_vault_balance', 'CASE WHEN game_vault_balance > 0 THEN game_vault_balance - (@sum_from_vault := 0.27 / 100 * game_vault_balance) ELSE @sum_from_vault := game_vault_balance END', FALSE);
        $this->db->set('members.available_game_balance', 'available_game_balance + @sum_to_second_table := (@sum_from_vault + ((110 / 100) * rating * @sum_from_vault))', FALSE);
        $this->db->set('members.game_balance_last_time', 'NOW()', FALSE);
        $this->db->set('settings.value', 'value + @sum_to_second_table', FALSE);
        $this->db->set('settings.datetime', 'NOW()', FALSE);
        $this->db->where('game_balance_last_time <= now() - INTERVAL 1 DAY');
        $this->db->where('settings.value', 'sett_name');
        $this->db->limit(1000);
        $this->db->order_by('rating', 'DESC');
        $this->db->update('members, settings');

But I have an error:

Error Number: 1146

Table 'my_database_name.members,' doesn't exist

UPDATE `members,` `settings` SET members.game_vault_balance = CASE WHEN game_vault_balance > 0 THEN game_vault_balance - (@sum_from_vault := 0.27 / 100 * game_vault_balance) ELSE @sum_from_vault := game_vault_balance END, members.available_game_balance = available_game_balance + @sum_to_second_table := (@sum_from_vault + ((110 / 100) * rating * @sum_from_vault)), members.game_balance_last_time = NOW(), settings.value = value + @sum_to_second_table, settings.datetime = NOW() WHERE `game_balance_last_time` <= now() - INTERVAL 1 DAY AND `settings`.`value` = 'sett_name' ORDER BY `rating` DESC LIMIT 1000
Oleg
  • 55
  • 7
  • how about getting the [id from the last updated record](https://stackoverflow.com/questions/20310298/get-the-id-of-the-last-updated-record) and build a new update query for the second table with the data from the last updated record? – Vickel Aug 04 '20 at 23:00
  • And using [transactions](https://codeigniter.com/userguide3/database/transactions.html) in case there could be several updates at the same time – Vickel Aug 04 '20 at 23:11
  • @Vickel, thanks for answer. I'm updated my post. I want to make query like in Update text, but it is not work. – Oleg Aug 05 '20 at 05:50
  • here are several answers, which could point you in the right direction: https://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query – Vickel Aug 05 '20 at 10:11

0 Answers0