同时更新两条不同字段

[Phoenix_99]

书名 价格 生效日 生效截止日
A 1.2 2017-04-1 2017-05-01
A 1.3 2017-05-01 2017-06-01
A 1.4 2017-06-01 null
B 1.2 2017-04-1 2017-05-01
B 1.3 2017-05-01 2017-06-01
B 1.4 2017-06-01 null

想同时把书名A，B的最后记录的生效日更新为2017-05-01，同时上一条的截止日更新为2017-05-01
如下：

A 1.2 2017-04-1 2017-05-01
A 1.3 2017-05-01 2017-05-01
A 1.4 2017-05-01 null
B 1.2 2017-04-1 2017-05-01
B 1.3 2017-05-01 2017-05-01
B 1.4 2017-05 -01 null

请教大家如何写更新SQL，谢谢。

[卖水果的net]

最后一条记录的 生效日 和 倒数第 二条记录的截止日，都更新成 2017-05-01 ？

[Phoenix_99]

是的， 请指教！

[jdsnhan]

数据上的规律存在不，比如生效截止日为null的就是最后一条记录了。 如果存在就好办了吧。 至于上一条的截止日期，可以用row_number() over(order by name desc ) 取行号为1的更新

[Phoenix_99]

最后1条，就是null

[CV工程师1996]

先获取A，B的最后记录的生效日的ID

先获取A，B的最后记录的生效日的ID
select  test.ID  from test where test.name='A' and test.Stime=(select MAX(test.Stime)from test)

select test.ID  from test where test.name='B' and test.Stime=(select MAX(test.Stime)from test)
再获取A，B 上一条的截止日的ID
select  test.ID from test where test.name='A' and test.ID=(select  test.ID  from test where test.name='A' and test.Stime=(select MAX(test.Stime)from test))-1

select test.ID  from test where test.name='B' and test.ID= (select test.ID  from test where test.name='B' and test.Stime=(select MAX(test.Stime)from test))-1

最后用case when  then 
UPDATE t SET t.Stime=CASE WHEN t.ID=(select test.ID  from test where test.name='A' and test.Stime=(select MAX(test.Stime)from test)) THEN '2017-05-01' WHEN t.ID=(select test.ID  from test where test.name='B' and test.Stime=(select MAX(test.Stime)from test)) THEN '2017-05-01'  ELSE t.Stime end 
, t.Etime=case when t.ID=(select  test.ID from test where test.name='A' and test.ID=(select  test.ID  from test where test.name='A' and test.Stime=(select MAX(test.Stime)from test))-1
) then '2017-05-01' WHEN t.ID=(select test.ID  from test where test.name='B' and test.ID= (select test.ID  from test where test.name='B' and test.Stime=(select MAX(test.Stime)from test))-1) THEN '2017-05-01'  ELSE t.Etime end
FROM test AS t

[诗和远方C]

UPDATE BOOK SET START_TIME='2018-09-19'
WHERE ID IN(
SELECT ID FROM(
SELECT ID,NAME,PRICE,START_TIME,END_TIME,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY 
START_TIME DESC)RN FROM BOOK
ORDER BY NAME,START_TIME DESC)
WHERE RN=1
)

UPDATE BOOK SET END_TIME='2018-09-19'
WHERE ID IN(
SELECT ID FROM(
SELECT ID,NAME,PRICE,START_TIME,END_TIME,
ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY 
START_TIME DESC)RN FROM BOOK
ORDER BY NAME,START_TIME DESC)
WHERE RN=2
)

[mzq3214]

Update
(select *(select 书名，价格，生效日，截止日，row_number () over (partition by 书名 order by 生效日 desc ) rn from Table t ) where rn=1)
set 生效日=date'2017-05-01'；

Update
(select *(select 书名，价格，生效日，截止日，row_number () over (partition by 书名 order by 生效日 desc ) rn from Table t ) where rn=2)
set 截止日=date'2017-05-01'；