phpmyadmin Question posted in
The official documentation can be found here.

UPDATE query not change data in MySQL database – Phpmyadmin

I’m writing an application in express.js. I’m using the “mysql” add-on (https://www.npmjs.com/package/mysql). I am sending an UPDATE query. The server returns me:

{"fieldCount": 0, "affectedRows": 1, "insertId": 0, "serverStatus": 2, "warningCount": 0, 
"message": "(Rows matched: 1 Changed: 0 Warnings: 0", " protocol41 ": true," changedRows ": 0}

I copy the query to phpMyAdmin and perform it correctly – it changes the data in the database.
Where do I make a mistake?

I send: “UPDATE measurements SET sugar_level = ‘429’, insulin_dose = ‘200’, hour_of_measurement = ’20’, date_of_measurement = ‘2019-07-29’
WHERE measurements.ID = ’14′” Query: INSERT INTO, DELETE FROM, SELECT working fine

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Problem solved. I've set the v-model wrong and the data to change was the same as the old one


  2. 0

    It looks like the UPDATE statement found one row that satisfied the conditions in the WHERE clause. That seems reasonable if the condition is an equality on the primary key or unique key columns, for example 

     UPDATE ... WHERE t.id = ?

    If all of the values assigned to columns match the values already stored, then MySQL will report that the row is not changed. It’s reasonable for a query like this 

    UPDATE mytable SET mycol = mycol

    to match all rows in the table, and to change zero rows.

    Note that the value returned for affectedRows is the value of matchedRows.

    It’s reasonable for matchedRows to have a different value than changedRows.

    NOTE: A BEFORE UPDATE trigger might affect the NEW.mycol value, so in that case, its reasonable to get a lot of rows changed.

    The serverStatus value of 2 is telling us that autocommit is enabled.

    Bottom line: the response from MySQL server is what we expect if we were to run a query 

     UPDATE mytable SET mycol = mycol WHERE id = ?

    Where we are finding exactly one row in the table that matches the condition, but after the assignment(s) are performed, the resulting row is identical to the row at the start of the statement i.e. there was no actual “change” made to the row.

    FOLLOWUP

    UPDATE measurements 
   SET sugar_level         = '429'
     , insulin_dose        = '200'
     , hour_of_measurement = '20'
     , date_of_measurement = '2019-07-29'
 WHERE measurements.ID = '14'

    Assuming that there is one row in the table with ID value of 14, and assuming no BEFORE UPDATE trigger on the table mucks with a value being assigned to a column

    Then it’s perfectly reasonable for the execution of this statement to “match” one row and “change” zero rows. In particular, after a second execution. If we were to actually change one of the values, say change sugar_level to ‘430’, then we would expect to see a changed row.

    I strongly suspect that MySQL is operating per the specification (i.e. “perform correctly”).

    (It’s not at clear why OP considers the result returned as incorrect, or believes the operation performed incorrectly.)

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

Back To Top
Search