[SOLVED] Column count doesn't match value count at row 1 complex query

Issue

I am inserting JSON data to mysql database and the query works perfect, then after adding a new column in the table structure, called ‘time’ of type DATETIME and default value current_timestamp, this error occurs:

Column count doesn’t match value count at row 1

I understand it is because there are less input values than the table has, but since it’s not simple query, i can’t manage to find a way to add time, it should be updated with NOW()

mysqli_query($mysqli, "SET @input := '$trimmed';");
  mysqli_query($mysqli, "INSERT INTO odds_changes SELECT jsontable1.*
  FROM JSON_TABLE(@input,
                  '$.kladionica[*]'
                  COLUMNS ( kladionica_name VARCHAR(64) PATH '$.name',
                            kladionica_utakmica VARCHAR(64) PATH '$.utakmica',
                            kladionica_utakmica_url VARCHAR(255) PATH '$.utakmica_url',
                            kladionica_kvota1 VARCHAR(64) PATH '$.kvota1',
                            kladionica_kvotax VARCHAR(64) PATH '$.kvotax',
                            kladionica_kvota2 VARCHAR(64) PATH '$.kvota2',
                            kladionica_kvota0_2 VARCHAR(64) PATH '$.kvota0_2',
                            kladionica_kvota3plus VARCHAR(64) PATH '$.kvota3plus'
                          )
                  ) jsontable1;");

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=66bd86f99005cd7d118dec411f02bec1

Solution

INSERT INTO odds_changes SELECT jsontable1.*,now()
FROM JSON_TABLE(@input,
                '$.kladionica[*]'
                COLUMNS ( kladionica_name VARCHAR(64) PATH '$.name',
                          kladionica_utakmica VARCHAR(64) PATH '$.utakmica',
                          kladionica_utakmica_url VARCHAR(255) PATH '$.utakmica_url',
                          kladionica_kvota1 VARCHAR(64) PATH '$.kvota1',
                          kladionica_kvotax VARCHAR(64) PATH '$.kvotax',
                          kladionica_kvota2 VARCHAR(64) PATH '$.kvota2',
                          kladionica_kvota0_2 VARCHAR(64) PATH '$.kvota0_2',
                          kladionica_kvota3plus VARCHAR(64) PATH '$.kvota3plus'
                         )
                ) jsontable1;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ebff70b061b18f1e41a314c492dbfafb

Answered By – Kendle

Answer Checked By – David Marino (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.