CODEWITHSUNDEEP
mysql
Alexey Bugerya
Alexey Bugerya

Reputation: 481

MySQL. Why I cant update one only one column?

I have table:

CREATE TABLE `cold_water_volume_value` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parameter_value_id` int(11) NOT NULL,
  `time` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4),
  `value` double NOT NULL,
  `device_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cold_water_volume_value_id_device_time` (`parameter_value_id`,`device_id`,`time`),
  KEY `idx_cold_water_volume_value_id_time` (`parameter_value_id`,`time`),
  KEY `fk_cold_water_volume_value_device_id_idx` (`device_id`),
  CONSTRAINT `fk_cold_water_volume_value_device_id` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`) ON UPDATE SET NULL,
  CONSTRAINT `fk_cold_water_volume_value_id` FOREIGN KEY (`parameter_value_id`) REFERENCES `cold_water_volume_parameter` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=684740 DEFAULT CHARSET=utf8;

And all rows have device_id = NULL. I want to update it by script:

UPDATE cold_water_volume_value SET device_id = 130101 WHERE parameter_value_id = 2120101;

But instead of replacing all device_id for picked parameter_value_id from null to given value, it sets all content of time and value columns to now () and some (seems like completely random from previous values) number.

Why it happens, and how to do it correct way?

Upvotes: 0

Views: 60

Answers (2)

Schwern
Schwern

Reputation: 165198

time is automatically updated as per your schema.

`time` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4) ON UPDATE CURRENT_TIMESTAMP(4)
                                                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

To get around that you can set time to itself in your update.

UPDATE cold_water_volume_value
  SET device_id = 130101, time = time
  WHERE parameter_value_id = 2120101;

But that is likely there to track when the last time a row was updated. If so it's working as intended, leave it to do its thing.

As for value, that might have an update trigger on it. Check with show triggers and look for triggers on that table.

Upvotes: 1

Lawrence E Bosumbe
Lawrence E Bosumbe

Reputation: 582

Your device_id is updated using content of time probably because in your index definition you mixed datatypes. It's worth noting that you should not mix datatypes especially on where clause when indexing.

Try to separate your indexes for example: KEY idx_cold_water_volume_value_id_device_time (time), KEY idx_cold_water_volume_value_id_device (parameter_value_id,device_id),

Try above statements for your definition and run query again.

It makes sense for the indexed column to have the same datatypes.
e.g. parameter_value_id and device_id

Upvotes: 0

Related Questions