Question 266520 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SELECT INTO always getting same ID

I have a Stored Procedure which takes as input a JSON List String in the following format:

[{"NOME":"Teste Produto, produo3 AAAAAAAAAAAAA","SKU":"ITAPE000011","IMG":"https://image.com","DESCRICAO":"Produto com total garantia","NCM":"90069190"},{"NOME":"NOV PRODUto dadsad","SKU":"ITAPE006666","IMG":"https://image.com","DESCRICAO":"Produto cadastrado novo","NCM":"85181090"},{"NOME":"Virgula,Produto, produo3 BBBBBBBBBBBBBBB","SKU":"ITAPE445566","IMG":"https://image3.com","DESCRICAO":"Produto Novo","NCM":"12345690000"}]

And inside this proc, I’m trying to use two SELECT INTO Statements to get the ID and hsCode (2 columns) before, for using it in a INSERT Statement inside a WHILE LOOP. But in every situation, the only ID that is inserted ’34’, which idk why is the first record of the table. It seems that the SELECT INTO is not working properly and getting the wrong record.

The Stored Procedure (With the input posted above):

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_InserirProdutosImport`(
IN produtos JSON
)
BEGIN
   DECLARE nome VARCHAR(100);
    DECLARE sku VARCHAR(20);
    DECLARE img VARCHAR(200);
    DECLARE ncm VARCHAR(30);
    DECLARE descricao VARCHAR(1000);
    
   DECLARE i INT DEFAULT 0;
    
   DECLARE idImposto INT;
    DECLARE idProduto INT;
    DECLARE varNcm VARCHAR(30);
    DECLARE hsCode VARCHAR(20);

    WHILE i < JSON_LENGTH(produtos) DO
        SET nome = NULL;
        SET sku = NULL;
        SET img = NULL;
        SET ncm = NULL;
        SET descricao = NULL;
        
        SET nome = JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].NOME')));
        SET sku = JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].SKU')));
        SET img = JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].IMG')));
        SET ncm = TRIM(JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].NCM'))));
                SET varNcm := TRIM(JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].NCM'))));
        SET descricao = JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].DESCRICAO')));

        SELECT ID INTO @idImposto
                FROM itapeimport.impostos WHERE ncm=varNcm LIMIT 1;
            
        SELECT hsCode INTO @hsCode
        FROM itapeimport.impostos WHERE ncm=varNcm LIMIT 1;

        INSERT INTO itapeimport.produtos(nome, sku, urlImagem, hsCode, ncm, descricao, idImposto,ultimoValor) 
        VALUES(nome, sku, img, @hsCode , ncm, descricao, @idImposto, 0);

        SELECT i + 1 INTO i;
    END WHILE;

END

INSERT result

The table which I’m trying to get the ID and hsCode

I idk what to do. I’m newcomer in MYSQL.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Tried both suggestions unsuccessfully

        SET @idImposto = (SELECT ID FROM itapeimport.impostos WHERE ncm=ncm LIMIT 1);
    SET @hsCode = (SELECT hsCode  FROM itapeimport.impostos WHERE ncm =ncm LIMIT 1);
    
    INSERT INTO itapeimport.produtos(nome, sku, urlImagem, hsCode, ncm, descricao, idImposto,ultimoValor) 
    VALUES(nome, sku, img, @hsCode , ncm, descricao, @idImposto, 0);

            SELECT ID INTO idImposto FROM itapeimport.impostos WHERE ncm=ncm LIMIT 1;
        SELECT hsCode INTO hsCode FROM itapeimport.impostos WHERE ncm =ncm LIMIT 1;
        
        INSERT INTO itapeimport.produtos(nome, sku, urlImagem, hsCode, ncm, descricao, idImposto,ultimoValor) 
        VALUES(nome, sku, img, hsCode , ncm, descricao, idImposto, 0);

    EDIT: Tried @nbk suggestion and changed every variable name and it started to work:

    CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_InserirProdutosImport`(
IN produtos JSON
)
BEGIN
    DECLARE varNome VARCHAR(100);
    DECLARE varSku VARCHAR(20);
    DECLARE varImg VARCHAR(200);
    DECLARE varNcm VARCHAR(30);
    DECLARE varDescricao VARCHAR(1000);
    
    DECLARE i INT DEFAULT 0;
    
    DECLARE varIdImposto INT;
    DECLARE varHsCode VARCHAR(20);
    
    DECLARE idProduto INT;

    WHILE i < JSON_LENGTH(produtos) DO
        
        SET varNome = JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].NOME')));
        SET varSku = JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].SKU')));
        SET varImg = JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].IMG')));
        SET varNcm = TRIM(JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].NCM'))));
        SET varDescricao = JSON_UNQUOTE(JSON_EXTRACT(produtos,CONCAT('$[',i,'].DESCRICAO')));
        
        SET varIdImposto = NULL;
        SET varHsCode = NULL;
        
        SELECT ID INTO varIdImposto FROM itapeimport.impostos WHERE ncm=varNcm LIMIT 1;
        SELECT hsCode INTO varHsCode FROM itapeimport.impostos WHERE ncm =varNcm LIMIT 1;
        
        IF varIdImposto IS NOT NULL AND varHsCode IS NOT NULL THEN
            INSERT INTO itapeimport.produtos(nome, sku, urlImagem, hsCode, ncm, descricao, idImposto,ultimoValor) 
            VALUES(varNome, varSku, varImg, varHsCode , varNcm, varDescricao, varIdImposto, 0);
        END IF;
    
        
        SELECT i + 1 INTO i;
    END WHILE;

END

  2. 0

    You fixed two problems in the procedure based on the other answer, but I want to show how your procedure could be a lot simpler, involving no loop and no local variables:

    CREATE PROCEDURE `sp_InserirProdutosImport`(
  IN produtos JSON
)
BEGIN
  INSERT INTO produtos (nome, sku, urlImagem, hsCode, ncm, descricao, idImposto, ultimoValor)
  SELECT j.nome, j.sku, j.img, i.hsCode, j.ncm, j.descricao, i.ID, 0
  FROM
  JSON_TABLE(produtos, '$[*]' COLUMNS (
      nome TEXT PATH '$.NOME',
      sku TEXT PATH '$.SKU',
      img TEXT PATH '$.IMG',
      descricao TEXT PATH '$.DESCRICAO',
      ncm BIGINT UNSIGNED PATH '$.NCM'
  )) AS j 
  LEFT OUTER JOIN impostos AS i USING (ncm);
END

    Demo on MySQL 8.0: https://dbfiddle.uk/awLBHmbC

    JSON_TABLE() converts a JSON document to a set of rows, and then the INSERT...SELECT inserts that set of rows, with extra values from the joined table, to your table produtos in one operation.

    SQL is usually more powerful when we think about data in sets, and use operations on sets, instead of thinking about data as collections of discrete items.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search