title: how do you increment a variable in an insert by picking the last id from another table at each row?
body: <p>Statement mysql / mariadb:</p>
  
  <p><code>max(so_id) 1</code>: obviously always returns the same number incremented the first time!</p>
  
  <pre><code>INSERT INTO sottoc
     (so_idgru, so_idconto, so_id, so_code, so_name)
  (SELECT cust_idgru, cust_idconto, 
          (SELECT max(so_id)+1
           FROM sottoc
           WHERE so_idgru=cust_idgru AND so_idconto=cust_idconto),
          cust_code, cust_name 
   FROM customer);
  </code></pre>
  
  <p>table <code>customer</code>:</p>
  
  <pre><code>cust_code   cust_idgru  cust_idconto
  001             2               2
  002             5               3       
  </code></pre>
  
  <p>table <code>sottoc</code>:</p>
  
  <pre><code>so_code     so_idgru        so_idconto  so_id
  </code></pre>
  
  <p>existing values:</p>
  
  <pre><code>010             2               2               1
  023             5               3               7
  </code></pre>
  
  <p>add record from customer it should come:</p>
  
  <pre><code>001             2               2               2   
  002             5               3               8
  </code></pre>
  
  <hr>
  
  <p>edit 22 ago 2018 - 14:50</p>
  
  <p>ok. Thanks for the answers and sorry if I was unclear.</p>
  
  <p>I try to explain myself in more detail:</p>
  
  <p>'customer' table, 3 fields and 2 records:
  <code>
  cust_code   cust_idgru  cust_idconto
  001             2               2
  002             5               3<br>
  </code></p>
  
  <p>'sottoc' table, 4 fields and 10 records : 
  <code>
  so_code     so_idgru        so_idconto      so_id
  010             2               2               1
  012             2               2               2
  015             2               2               3
  023             5               3               1
  027             5               3               2
  030             5               3               3
  023             5               3               4
  023             5               3               5
  023             5               3               6
  023             5               3               7
  </code></p>
  
  <p>The primary key of 'sottoc' is composed of so_idgru+so_idconto+so_id and that so_id is progressive within the partial key so_id_gru+so_idconto</p>
  
  <p>(I know it would have been better id progressive single, but this has been passed to me and on this I must to work!)</p>
  
  <p>As shown in the example 2-2-1, 2-2-2, ... 5-3-1, 5-3-2, ...</p>
  
  <p>so_id CAN NOT be renumbered because it is related to other tables!</p>
  
  <p>I have to add the 2 'customer' records in order to have the 'sottoc' table :</p>
  
  <p><code>
  so_code     so_idgru        so_idconto      so_id
  010             2               2               1
  012             2               2               2
  001             2               2               3    &lt;--- 
  015             2               2               3
  023             5               3               1
  027             5               3               2
  030             5               3               3
  023             5               3               4
  023             5               3               5
  023             5               3               6
  023             5               3               7
  002             5               3               8   &lt;---
  </code></p>
  
  <p>Thanks</p>
  
  <p>Enrico</p>
  
  <hr>
  
  <p>For now I used this way:</p>
  
  <p><code></p>
  
  <pre><code>BEGIN
      DECLARE cursor_idgru INT;
      DECLARE cursor_idconto INT;
      DECLARE cursor_code VARCHAR(50);
      -- cursor for distinct cust_idgru+cust_idconto
      DECLARE cursor_i CURSOR FOR 
          SELECT cust_idgru, cust_idconto
              FROM customer 
  
              GROUP BY cust_idgru, cust_idconto;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
      -- cursor loop
      OPEN cursor_i;
      read_loop: LOOP
          FETCH cursor_i INTO cursor_idgruppo, cursor_idconto;
          IF done THEN
              LEAVE read_loop;
          END IF;
          -- last so_id of the cursor_idgru/cursor_idconto
          SET @uidsottoc = (SELECT max(so_id) FROM sottoc WHERE so_idgru=cursor_idgru AND so_idconto=cursor_idconto);
          IF(@uidsottoc IS NULL) THEN
              SET @uidsottoc := 0;
          END IF;
          INSERT INTO sottoc
              (so_idgru, so_idconto, so_id, so_code, so_name)
               (SELECT cust_idgru, cust_idconto, @uidsottoc := @uidsottoc + 1, cust_code, cust_name
                  FROM customer
                  WHERE cust_idgru=cursor_idgru AND cust_idconto=cursor_idconto);
          COMMIT;
      END LOOP;
      CLOSE cursor_i;
  END
  </code></pre>
  
  <p></code></p>
  
  <p>thank you all</p>
  
  <p>Enrico</p>