更新sql文语句

Question

书名                    价格                    生效日                  生效截止日
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              nullnull每本书的最后1条记录，把每本书的最后1条的生效日和倒数第2条生效截止日，更新为2017-05-01,
请教大家如何写更新SQL，谢谢。

Answer 1

UPDATE TEST2 T1
   SET T1.E_DT = '20170501'
 WHERE EXISTS (SELECT 1
          FROM (SELECT NAME, MAX(E_DT) MX_DT FROM TEST2 GROUP BY NAME) T2
         WHERE T1.NAME = T2.NAME
           AND T1.E_DT = T2.MX_DT);UPDATE TEST2 T1
   SET T1.S_DT = '20170501'
 WHERE EXISTS (SELECT 1
          FROM (SELECT NAME, MAX(S_DT) MX_DT FROM TEST2 GROUP BY NAME) T2
         WHERE T1.NAME = T2.NAME
           AND T1.S_DT = T2.MX_DT);