Encountering a MySQL Server error while attempting to insert exclusive records using the ROWCOUNT function

Currently, I am experimenting with some JavaScript code in Node.js, utilizing the MySQL package within Node and running MySQL queries in the Visual Studio Code terminal.

Despite trying various approaches such as adding (), '', "", etc., isolating the update and insert statements individually, and executing them against a table in PHPMyAdmin—both statements perform correctly. The issue arises when incorporating "IF @@ROWCOUNT=0" into the statement, causing the entire query to fail.

The script executes the following SQL statement stored in a JavaScript variable:

var sql = "UPDATE players SET userID='123',username='FNNewUser',site='blah' WHERE userID='123' IF @@ROWCOUNT=0 PRINT 'NO ROWS UPDATED'";

Upon executing the script, it initially appears to be successful but subsequently throws an error message:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF @@ROWCOUNT=0 PRINT 'NO ROWS UPDATED'' at line 1
    at Query.Sequence._packetToError (C:\node\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    ...

The primary objective is to verify if a record already exists in MySQL; if not, proceed with the insert statement. While this query might have been addressed on StackOverflow, I am intrigued by why introducing the IF conditional causes the SQL statement to fail.

javascriptmysqlsqlrowcount

Answer №1

It seems that your MySQL query is not correctly formatted. To fix this issue, simply execute the UPDATE statement and then verify if any records were affected by using the node-js method changedRows:

var sql = "UPDATE users SET username='NewUser',email='newuser@example.com' WHERE id=1";
connection.query(sql, function (error, results, fields) {
    if (error) throw error;
    if (results.changedRows == 0) console.log('No rows were impacted');
});

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Typescript issue: "The property 'webPreferences' is causing an expected type error, as declared in type 'BrowserWindowConstructorOptions'"

Struggling to get unredacter by bishop fox up and running. Despite my best efforts, I can't seem to compile the code. I tried debugging it in VS Code, but since I only have a basic knowledge of HTML and no experience with TypeScript or JavaScript, I&a ...

javascriptnode.jstypescript

How to resolve preventDefault issue on else condition during form submission in CoffeeScript on Rails 4

After submitting a form, I have implemented a code to prevent the page from refreshing and perform different actions based on certain conditions. Everything works as expected except for one scenario where the page still refreshes after executing the ELSE c ...

javascriptjqueryruby-on-rails-4coffeescript

Get only a single record by extracting data from multiple tables in MySQL

I'm faced with a challenge involving two tables: one stores blogs and the other stores images related to those blogs. The image table contains columns for id, blog_id, and image_name. Given that a blog can have multiple images attached to it, I' ...

phpmysql

Choose the value of the dynamically changing id with the same name using jQuery

Whenever I choose the id (they all have the same id number which is fetched dynamically), it displays the value of the first id. Here's how I'm trying to implement it, but it doesn't seem to be working: function editdr() { qty = $(thi ...

javascriptjqueryajax

Exploring the Possibilities of Socket.io Integration with Express 4 Across Multiple Pages: Dive Into Socket.io Sample Code

Just to clarify, I came across a similar question on Stack Overflow before posting this. However, the answer there was not clear to me and my query is slightly different. Thus, I am hoping for a more straightforward explanation. The Express Generator sets ...

javascriptnode.jsexpresssocket.io

SQL Server: PHP causing database to become stuck in "Restoring" state

Whenever I try to restore a database using SQL Server Management Studio, everything goes smoothly without any issues. However, when I attempt to restore it through PHP (using sqlsrv_query function), the database seems to get stuck in a "Restoring..." st ...

phpsqlsql-serversqlsrv

Tips for automatically filling out a div class form:

I currently have an Autoresponder email form featured on my webpage. Here is a snippet of the code for the section where customers enter their email: <div id = "af-form-45" class = "af-form" > <div id = "af-body-45" class = "af-body af-standa ...

javascriptjqueryclasshtml

I am looking for a solution to transform JSON data into XML format using node.js

I'm looking to convert JSON data on my node.js server into an RSS feed. Can anyone recommend the most efficient method for accomplishing this task? Additionally, once the conversion is complete, I will need the RSS file to be outputted or overwritten. ...

javascriptxmljsonnode.jsrss

How can I enhance speed and efficiency when transferring data into MySQL databases?

I am currently developing a website using Django and MySQL (MyISAM) as the backend. The database data is being imported from multiple XML files processed by an external script and output as a JSON file. Whenever a new JSON file differs from the previous on ...

pythonmysqlxmldjangojson

I need to press the button two times to successfully submit

I am experiencing a remote validation issue. When I click on the submit button without focusing on the text box, it triggers a remote ajax call for validation. However, when I press the submit button a second time, the form gets submitted. On the same cl ...

javascriptphpajax

Insert data into a drop-down menu and organize it

When I choose a value in one Select, the other options are removed and the previous value is added to them. How can I use JQuery to add the previous value to Select and then sort it alphabetically? ...

javascriptjqueryhtml

Tips for initiating a function at a designated scroll point on a webpage

Currently, I am testing out a code snippet that allows images to flip through in a canvas element. I'm curious if it's possible to delay the image flipping effect until the viewer scrolls down to a specific section of the page where the canvas i ...

javascripthtmljquerycanvashtml5-canvas

What could be causing errors with my addrole command in discord.jsV12?

After updating to discord.jsv13, my addrole command is no longer working properly. I keep getting errors like role.id is not a function or role.id is not defined, even though I can't seem to find any errors in my code. I have experience with JavaScrip ...

javascriptnode.jsdiscorddiscord.jsbots

A guide on triggering a function when a button is clicked in reactjs

Can anyone please help me with this issue I'm having: how do I execute a function when a button is clicked? I currently have the following code but it's not working export var Modulo = React.createClass({ prom1: function () { retur ...

javascriptreactjsmodule

Transmit information to PHP via JSON with Cross Domain communication

My code is server1 and PHP code is server2. These servers are not connected. What issues can arise from this setup? var req = new XMLHttpRequest(); req.open("POST", "http://www.example.com/form/data.php", true); req.setRequestHeader("Content-type", "ap ...

phpjavascriptajaxjson

Is there a way to retrieve the properties of another function within the same component?

I am trying to place NumberFormat inside OutlinedInput and I also need different properties for the format of NumberFormat. (There will be a select window that defines which format property to use). This is what I have: import OutlinedInput from "@ma ...

javascriptreactjsmaterial-ui

Storing JSON data in an array using JavaScript is a powerful method to

Here is an example of JSON data: [{ "address": "A-D-1", "batch": [{ "batch_number": "B-123", "cost": [{ "cost": "C1" }] }] }, { "address": "A-85-1", "batch": [{ "batch_number": "B-6562", ...

javascriptjson

How can one easily retrieve the callback function arguments from outside the function?

Here is a snippet of my code: var jenkins = require('jenkins')('http://192.168.1.5:8080'); var job_name = undefined; jenkins.job.list(function doneGetting(err, list) { if (err) throw err; job_name = list[0].name; }); jenkins. ...

javascriptnode.jsexpressnpm

Solving a multitude of connections in a MYSQL database

I currently have three tables in my database: users(id), userproduct(user_id, product_id), and product(id). When a user adds a product, their user_id and the product_id are saved in the USERPRODUCT table. Now, at the same time, another user can save a pr ...

phpmysqldatabasemany-to-many

How can I determine if there is text contained within an HTML tag?

Imagine a scenario where I want to retrieve all text from a specific HTML tag: Here is the example HTML: <div id="container"> <div id="subject"> <span><a></a></span> </div> </div> var ...

javascripthtmltags