0

In my table I have some rows with same 'stock_id', and need update with 2 conditions, but don't work, just update all rows with value 1

$this->db->query('UPDATE stock_table SET size = IF(kit = \'n\', 1, (1 * 10)) WHERE id = '.$row->stock_id);

But my size have S - M - X with same stock_id

Eg: I need update qtde column where stock = 13 and size = S

Everton Thomazi
  • 27
  • 4
  • 1
    what two conditions? I only see one 'id = X'. – danblack Feb 11 '20 at 00:24
  • 1
    `1 * 10` is just `10`. – Barmar Feb 11 '20 at 00:26
  • @danblack The other condition is the `IF()` – Barmar Feb 11 '20 at 00:26
  • Are you sure there are kits other than `n`? – Barmar Feb 11 '20 at 00:28
  • I need update column 'qtde' where eg. 'stock_id' = 13 AND tamanho = 's' – Everton Thomazi Feb 11 '20 at 00:30
  • Is `size` a string like S, M, X, or a number like 1 or 10? – Barmar Feb 11 '20 at 00:37
  • **WARNING**: Whenever possible use **prepared statements with placeholder values** to avoid injecting arbitrary data in your queries and creating [SQL injection bugs](http://bobby-tables.com/). These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. – tadman Feb 11 '20 at 00:38
  • *"I need update column 'qtde' where eg. 'stock_id' = 13 AND tamanho = 's'"* - Add an additional clause with the `AND` operator. – Funk Forty Niner Feb 11 '20 at 00:40
  • @Barmar 'size' it's a column with values S, M, X and 'qtde' it's another column with number 1 to 10 – Everton Thomazi Feb 11 '20 at 00:43
  • So should it be `SET qtde` instead of `SET size`? – Barmar Feb 11 '20 at 00:46

1 Answers1

1

Add the other condition with AND in the WHERE clause.

$this->db->query('UPDATE stock_table SET qtde = IF(kit = \'n\', 1, (1 * 10)) WHERE size = \'S\' AND id = '.$row->stock_id);

BTW, if $row is the result of another query, you can use UPDATE + JOIN to do this in one query, instead of looping. See MySQL update table based on another tables value

Barmar
  • 741,623
  • 53
  • 500
  • 612