Description:
I'm using an update with a join to change some fields in a table. This table has two timestamp fields but I don't want those to change, so I'm using the
"foo.time = foo.time" trick in the update query. However, MySQL still changes the first timestamp of every changed row to the current time.
How to repeat:
DROP TABLE IF EXISTS Widgets;
CREATE TABLE Widgets (
widget_id INT NOT NULL,
t_begin TIMESTAMP NOT NULL,
t_end TIMESTAMP NOT NULL,
some_data VARCHAR(10),
UNIQUE INDEX (widget_id),
INDEX (t_begin),
INDEX (t_end)
);
DROP TABLE IF EXISTS Gadgets;
CREATE TABLE Gadgets (
time TIMESTAMP NOT NULL,
widget_id INT NOT NULL,
INDEX (time)
);
INSERT INTO Widgets VALUES
( 1, '20030724000000', '20030724235959', "" ),
( 2, '20030724000000', '20030724235959', "" ),
( 3, '20030724000000', '20030724235959', "" ),
( 4, '20030724000000', '20030724235959', "" );
INSERT INTO Gadgets VALUES
( '20030724110000', 1 ),
( '20030724120000', 2 ),
( '20030724130000', 3 ),
( '20030724140000', 4 );
UPDATE Widgets, Gadgets
SET Widgets.t_begin = Widgets.t_begin,
Widgets.some_data = 'YES!'
WHERE Widgets.widget_id = Gadgets.widget_id
AND Gadgets.time between '20030724120000' and '20030724130000';
Now
SELECT * FROM Widgets;
... produces:
+-----------+----------------+----------------+-----------+
| widget_id | t_begin | t_end | some_data |
+-----------+----------------+----------------+-----------+
| 1 | 20030724000000 | 20030724235959 | |
| 2 | 20030724142059 | 20030724235959 | YES! |
| 3 | 20030724142059 | 20030724235959 | YES! |
| 4 | 20030724000000 | 20030724235959 | |
+-----------+----------------+----------------+-----------+
4 rows in set (0.00 sec)
... where I would have expected:
+-----------+----------------+----------------+-----------+
| widget_id | t_begin | t_end | some_data |
+-----------+----------------+----------------+-----------+
| 1 | 20030724000000 | 20030724235959 | |
| 2 | 20030724000000 | 20030724235959 | YES! |
| 3 | 20030724000000 | 20030724235959 | YES! |
| 4 | 20030724000000 | 20030724235959 | |
+-----------+----------------+----------------+-----------+
4 rows in set (0.00 sec)
Description: I'm using an update with a join to change some fields in a table. This table has two timestamp fields but I don't want those to change, so I'm using the "foo.time = foo.time" trick in the update query. However, MySQL still changes the first timestamp of every changed row to the current time. How to repeat: DROP TABLE IF EXISTS Widgets; CREATE TABLE Widgets ( widget_id INT NOT NULL, t_begin TIMESTAMP NOT NULL, t_end TIMESTAMP NOT NULL, some_data VARCHAR(10), UNIQUE INDEX (widget_id), INDEX (t_begin), INDEX (t_end) ); DROP TABLE IF EXISTS Gadgets; CREATE TABLE Gadgets ( time TIMESTAMP NOT NULL, widget_id INT NOT NULL, INDEX (time) ); INSERT INTO Widgets VALUES ( 1, '20030724000000', '20030724235959', "" ), ( 2, '20030724000000', '20030724235959', "" ), ( 3, '20030724000000', '20030724235959', "" ), ( 4, '20030724000000', '20030724235959', "" ); INSERT INTO Gadgets VALUES ( '20030724110000', 1 ), ( '20030724120000', 2 ), ( '20030724130000', 3 ), ( '20030724140000', 4 ); UPDATE Widgets, Gadgets SET Widgets.t_begin = Widgets.t_begin, Widgets.some_data = 'YES!' WHERE Widgets.widget_id = Gadgets.widget_id AND Gadgets.time between '20030724120000' and '20030724130000'; Now SELECT * FROM Widgets; ... produces: +-----------+----------------+----------------+-----------+ | widget_id | t_begin | t_end | some_data | +-----------+----------------+----------------+-----------+ | 1 | 20030724000000 | 20030724235959 | | | 2 | 20030724142059 | 20030724235959 | YES! | | 3 | 20030724142059 | 20030724235959 | YES! | | 4 | 20030724000000 | 20030724235959 | | +-----------+----------------+----------------+-----------+ 4 rows in set (0.00 sec) ... where I would have expected: +-----------+----------------+----------------+-----------+ | widget_id | t_begin | t_end | some_data | +-----------+----------------+----------------+-----------+ | 1 | 20030724000000 | 20030724235959 | | | 2 | 20030724000000 | 20030724235959 | YES! | | 3 | 20030724000000 | 20030724235959 | YES! | | 4 | 20030724000000 | 20030724235959 | | +-----------+----------------+----------------+-----------+ 4 rows in set (0.00 sec)