Search code examples
mysqlheidisql

Update Query issue . 0 rows updated when there should have been 1. HeidiSQL

I have a table like this:

ALTER TABLE `MW_Locations`  CHANGE COLUMN `ID` `ID` INT(20) NULL DEFAULT NULL FIRST,    CHANGE COLUMN `UDID` `UDID` INT(20) NULL DEFAULT NULL AFTER `ID`,   CHANGE COLUMN `IDACCOUNT` `IDACCOUNT` INT(20) NULL DEFAULT NULL AFTER `UDID`,   CHANGE COLUMN `Name` `Name` VARCHAR(50) NULL DEFAULT NULL AFTER `IDACCOUNT`,    CHANGE COLUMN `Lat` `Lat` DOUBLE NULL DEFAULT NULL AFTER `Name`,    CHANGE COLUMN `Lon` `Lon` DOUBLE NULL DEFAULT NULL AFTER `Lat`,     CHANGE COLUMN `IDMWDevice` `IDMWDevice` INT(50) NULL DEFAULT NULL AFTER `Lon`,  CHANGE COLUMN `Desc` `Desc` VARCHAR(50) NULL DEFAULT NULL AFTER `IDMWDevice`,   CHANGE COLUMN `IDAction` `IDAction` INT(20) NULL DEFAULT NULL AFTER `Desc`,     CHANGE COLUMN `Status` `Status` VARCHAR(50) NULL DEFAULT NULL AFTER `IDAction`,     ADD PRIMARY KEY (`IDACCOUNT`);

The issue is I can´t make any update.Like this one:

UPDATE `MW_Locations` SET `IDACCOUNT`=1 WHERE  `ID`=0 AND `UDID`=0 AND `IDACCOUNT`=0 AND `Name`='Panteon' AND `Lat`=25.6789521128695 AND `Lon`=100.336310863495 AND `IDMWDevice`=0 AND `Desc`='' AND `IDAction`=0 AND `Status`='' LIMIT 1;

Im using heidisql connecting to a server The rows I have made are these ones:enter image description here

Want to make all the rows I have of the column IDACCOUNT with the value "1" So it would be assigned to a user 1 in my database, later bring in a json that array of data to make some tasks. Also I want to make ID increment but it brings me an error too.

Solution

  • This query fails, because you demand exact equality for your DOUBLE fields Lat and Lon. Those columns are not needed to identify your row. Rewrite your sql statement to

    UPDATE `MW_Locations` 
   SET `IDACCOUNT`=1 
 WHERE `Name`='Panteon';

    This value in this column is enough to identify your row.

    I cite from the MySQL manual:

    Because floating-point values are approximate and not stored as exact values, attempts to treat them as exact in comparisons may lead to problems.