The answer was for the first question, but the meaning doesn't change too much, and for the second, only more variables need to be indicated.Table for test:CREATE TABLE `table` (
`id` INT NOT NULL AUTO_INCREMENT,
`time` TIMESTAMP,
`status` varchar(55),
PRIMARY KEY (id)
);
INSERT INTO table
(id, time, status)
VALUES
(1, FROM_UNIXTIME(1000), 'wait'),
(2, FROM_UNIXTIME(10000), 'success')
;
I see.id time status
1 January, 01 1970 00:16:40 wait
2 January, 01 1970 02:46:40 success
The request is made (Temporary submissions are made separately illustratively, although they can be sent directly to the request text). http://sqlfiddle.com/#!9/c2d1c/1 SET @id=3, @status='wait';
insert into table (id, time, status)
values (@id, CURRENT_TIMESTAMP, @status)
on duplicate key update status=@status, time=(
SELECT IFNULL(t2.time, CURRENT_TIMESTAMP)
FROM table t1
LEFT JOIN table t2 ON t2.id=@id AND t2.status=@status
WHERE t1.id=@id
);
This result is:id time status
1 January, 01 1970 00:16:40 wait
2 January, 01 1970 02:46:40 success
3 September, 20 2015 14:07:17 done
http://sqlfiddle.com/#!9/37337/1 Modified at entranceSET @id=1, @status='wait';
Outcomeid time status
1 January, 01 1970 00:16:40 wait
2 January, 01 1970 02:46:40 success
http://sqlfiddle.com/#!9/43a82/1 RemovedSET @id=1, @status='done';
Outcomeid time status
1 September, 20 2015 14:08:35 done
2 January, 01 1970 02:46:40 success
P. S. http://habrahabr.ru/post/156489/ requests for type INSERT… ON DUPLICATE KEY UPDATE and AUTO_INCREMENT and their behavior.UPD Request for question from your commentCREATE TABLE table (
id INT NOT NULL AUTO_INCREMENT,
source INT NOT NULL,
source_id INT NOT NULL,
time TIMESTAMP,
status varchar(55),
rating INT NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY (source, source_id)
);
INSERT INTO table
(id, source, source_id, time, status, rating)
VALUES
(1, 1, 123, FROM_UNIXTIME(1000), 'wait', 1000),
(2, 1, 456, FROM_UNIXTIME(2000), 'done', 2000),
(3, 2, 123, FROM_UNIXTIME(3000), 'wait', 3000)
;
SET @source=1, @source_id=123, @status='wait', @rating=555;
insert into table (source, source_id, time, status, rating)
values (@source, @source_id, CURRENT_TIMESTAMP, @status, @rating)
on duplicate key update time=(
SELECT IFNULL(t2.time, CURRENT_TIMESTAMP)
FROM table t1
LEFT JOIN table t2 ON t2.source=@source AND t2.source_id=@source_id AND t2.status=@status
WHERE t1.source=@source AND t1.source_id=@source_id
), status=@status, rating=IF(time<>CURRENT_TIMESTAMP, rating, @rating);
Test http://sqlfiddle.com/#!9/921be/1 Since you are id You don't use it, we're gonna have to check on the couple. source and source_id♪ rating and other columns (if any) change depending on time by condition IF(time<>CURRENT_TIMESTAMP, ...) I don't want you to give me any requests.