title: how do i update column position into table with several conditions mysql
body: <p>I have a table like this:</p>
  <pre><code>+----+------------+-------------+----------+------------+-----------+
  | id | product_id | category_id | position | createdAt  | status_id |
  +----+------------+-------------+----------+------------+-----------+
  |  1 |        246 |           1 |        1 | 2020-01-01 |         1 |
  |  2 |        247 |           1 |        2 | 2020-01-02 |         0 |
  |  3 |        248 |           8 |        3 | 2020-01-03 |         1 |
  |  4 |        249 |           8 |        4 | 2020-01-04 |         0 |
  |  5 |        250 |           1 |        5 | 2020-01-04 |         1 |
  |  6 |        251 |           8 |        6 | 2020-01-05 |         1 |
  +----+------------+-------------+----------+------------+-----------+
  </code></pre>
  <p>how do i make an order and update the column position for product_id with category_id = 8 and status_id = 1, but if status_id being updated from 1 to 0, then remove the order on that product_id, based on that table, my expected result should be like this:</p>
  <pre><code>+----+------------+-------------+----------+------------+-----------+
  | id | product_id | category_id | position | createdAt  | status_id |
  +----+------------+-------------+----------+------------+-----------+
  |  1 |        246 |           1 |        1 | 2020-01-01 |         1 |
  |  2 |        247 |           1 |        2 | 2020-01-02 |         0 |
  |  3 |        248 |           8 |        1 | 2020-01-03 |         1 |
  |  4 |        249 |           8 |        4 | 2020-01-04 |         0 |
  |  5 |        250 |           1 |        5 | 2020-01-04 |         1 |
  |  6 |        251 |           8 |        2 | 2020-01-05 |         1 |
  +----+------------+-------------+----------+------------+-----------+
  </code></pre>
  <p>i've tried with</p>
  <pre><code>    UPDATE position SET position = (how to order it) 
      WHERE category_id = 8 and status_id = 1
      
  </code></pre>
  <p>UPDATE :
  i've tried with this :</p>
  <pre><code>UPDATE product_categories pc1 SET pc1.position = 
  (SELECT  (@row:=@row+1) FROM product_categories pc2 WHERE
  pc2.status = 1 AND pc2.category_id = 8 ORDER BY pc2.createdAt);
  </code></pre>
  <p>but it produces an error like this <code>Error Code: 1093. You can't specify target table 'pc1' for update in FROM clause</code></p>
  <p>UPDATE: I've tried with this</p>
  <pre><code>SET @row := 0;
  SET SQL_SAFE_UPDATES = 0;
  UPDATE product_categories pc1 SET pc1.position = 
  (SELECT  (@row:=@row+1) FROM (SELECT pc2.product_id FROM product_categories pc2 WHERE
  pc2.status = 1 AND pc2.category_id = 8 ORDER BY pc2.createdAt) AS a);
  SET SQL_SAFE_UPDATES = 1;
  </code></pre>
  <p>and it appears an error like this : <code>Error Code: 1242. Subquery returns more than 1 row</code></p>