如何在MySQL中插入最新记录并停用其他记录

Question

我有样本数据

ID Name   val  Active 
1  Mohan  5     1
2  Mohan  10    1

我想在每次运行查询时克隆记录

ID Name   val  Active 
1  Mohan  5     0
2  Mohan  10    0
3  Mohan  5     1
4  Mohan  10    1

查询

Update Table  SET  Active  = 0 WHERE  Name = 'Mohan'
INSERT INTO TABLE(ID,Name,val,Active )
SELECT  ID,Name,val,1FROM TABLE WHERE  Name = 'Mohan'

当我再次运行这个相同的查询时，它插入了4条记录，我只想插入最新的非活动记录作为活动记录(即2条记录(

Answer 1

您的两个查询可能会被一个取代

INSERT INTO TABLE(ID, Name, val, Active)
SELECT  ID, Name, val, 0
FROM TABLE 
WHERE Name = 'Mohan'
AND Active = 1

小提琴

Answer 2

您可以使用cross join生成行：

select 'Mohan' as n.name, v.val, a.active
from (select distinct active from t) a cross join
(select distinct val from t) v ;

目前还不清楚id是否是你想要传递的东西。如果是，你可以使用row_number():

select row_number() over (order by name, val, active) as id,
'Mohan' as n.name, v.val, a.active
from (select distinct active from t) a cross join
(select distinct val from t) v ;