At the same time update the two different fields-CodePudding

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
1.4 2 B 017-05-01 null

Consult everybody how to write SQL update, thanks.

CodePudding user response:

The last record of the effective date and the deadline, the penultimate record update into the 2017-05-01?

CodePudding user response:

Yes, please advise!

CodePudding user response:

Data on laws don't exist, such as the effective deadline to null is the last record,
If there is a right,
As for a deadline, can use the row_number () over (order by name desc) 1 take line number for the update

CodePudding user response:

The last article 1, is null

CodePudding user response:

To obtain A and B at the end of the record of the effective date of ID

 first obtain A, B the last record of the effective date of the ID of the 
Select the test. The ID from the test where the test. The name='A' and the test. The Stime=(select MAX (test. Stime) from test) 

Select test. The ID from the test where the test. The name='B' and the test. The Stime=(select MAX (test. Stime) from test) 
To obtain A, B A deadline on the ID of the 
Select the test. The ID from the test where the test. The name='A' and the test. The ID=(select test. The ID from the test where the test. The name='A' and the test. The Stime=(select MAX (test. Stime) from test)) - 1 

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

With the case finally the when then 
UPDATE t SET t.S time=CASE WHEN t.I D=(select test. The ID from the test where the test. The name='A' and the test. The Stime=(select MAX (test. Stime) from test)) THEN '2017-05-01' WHEN t.I D=(select test. The ID from the test where the test. The name='B' and the test. The Stime=(select MAX (test. Stime) from test)) THEN '2017-05-01' ELSE t.S time end 
, t.E time=case when t.I D=(select test. The ID from the test where the test. The name='A' and the test. The ID=(select test. The ID from the test where the test. The name='A' and the test. The Stime=(select MAX (test. Stime) from test)) - 1 
) then the '2017-05-01 s' the WHEN t.I D=(select test. The ID from the test where the test. The name=' B 'and the test. The ID=(select test. The ID from the test where the test. The name=' B 'and the test. The Stime=(select MAX (test. Stime) from test)) - 1) then' 2017-05-01 'ELSE t.E time end 
FROM the test AS the t

CodePudding user response:

 

UPDATE the 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 the NAME the ORDER BY 
START_TIME DESC) RN FROM the BOOK 
The ORDER BY NAME, START_TIME DESC) 
WHERE an RN=1 
) 

UPDATE the 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 the NAME the ORDER BY 
START_TIME DESC) RN FROM the BOOK 
The ORDER BY NAME, START_TIME DESC) 
WHERE an RN=2 
)

CodePudding user response:

The Update
(select * (select title, price, effective date, deadline, row_number () over (partition by title order by desc) the effective date rn from Table t) where rn=1)
Set the effective date=the date '2017-05-01';

The Update
(select * (select title, price, effective date, deadline, row_number () over (partition by title order by desc) the effective date rn from Table t) where rn=2)
The set deadline date='2017-05-01';