Complex mysql query in node.js?

Question

MySQL

I work with this library: https://github.com/mysqljs/mysql
I want to make two mysql queries in one, I do this:

connection.query('INSERT INTO Items SET ?', {itemid: 'SELECT MAX(`itemid`) FROM `Items`', title: data.title, type: data.group}).then(function(result){
                console.log(result);
            }).catch(function(error){
                console.log(error);
            });

The cast is sent successfully, but the itemid, as I understand it, does not work, and it does not reach the table.
I am more than sure that I am doing something wrong in the request itself, that this cannot be done. I did not find complex queries in the documentation. How to be?

Answer 1

Your query after substitution of values ​​looks something like this:
That is, the request code for getting the itemid value is enclosed in quotes - no request occurs, a string is written to the itemid. What seems to be the cause of the error - itemid is a number, right? How to insert sql code into a query from a variable, I myself have no idea, and I’m not at all sure that this can be done.
Besides, there is another problem. If you try to manually execute your request (through phpmyadmin or something else), you will get a syntax error message - getting the itemid value should be surrounded by parentheses: Okay, put the brackets. Is everything good now? No way - we get the error "You can't specify target table 'Items' for update in FROM clause". A correct insert into a table with data retrieved from the same table would look something like this:

INSERT INTO Items(itemid, title, type)
SELECT MAX(itemid), <значение для title>, <значение для type> FROM Items

In general, try something like this:

connection.query('INSERT INTO Items(itemid, title, type) SELECT MAX(itemid), ?, ? FROM Items', [ data.title, data.group ])