The title effect price effective date deadline
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
1.2 the 2017-04-1 2017-05-01 B
1.3 the 2017-05-01 2017-06-01 B
1.4 the 2017-06-01 B null

Want to put the title at the same time A and B records the effective date of the last update for the 2017-05-01, at the same time on A deadline to update to the 2017-05-01
As follows:

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
1.2 the 2017-04-1 2017-05-01 B
1.3 B 2017-05-01 2017-05-01
B 1.4 2017-05-01 null

Null last 1 records of every book, every book at the end of the article 1 second from bottom of the effective date and the effective deadline, updated to 2017-05-01,
Consult everybody how to write SQL update, thanks.

CodePudding user response:

The

refer to the original poster Phoenix_99 response:

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

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