我的要求是选择HIST_START
值,其中status="CANCELLED" AND table="OUTCOME_STATUS"
。将此值放在HIST_END
列中,其中table=("DOC_STATUS" OR "ENTRY_STATUS") AND HIST_END is NULL
如。第一行和第二行应该将HIST_END
作为12/06/2012
(即最后一行的HIST_START
)。
ID HIST_START HIST_END Appl Number code version STATUS TABLE
10298337 4/06/2012 1 I45 1 PENDING DOC_STATUS
10298337 4/06/2012 1 I45 1 PENDING ENTRY_STATUS
10298337 4/06/2012 5/06/2012 1 I45 1 PENDING OUTCOME_STATUS
10298337 5/06/2012 11/06/2012 1 I45 1 ADM_REV OUTCOME_STATUS
10298337 11/06/2012 11/06/2012 1 I45 1 BAMERROR OUTCOME_STATUS
10298337 11/06/2012 12/06/2012 1 I45 1 AWAIT_DOCS OUTCOME_STATUS
10298337 12/06/2012 1 I45 1 CANCELLED OUTCOME_STATUS
Thanks in advance.
我相信status="CANCELLED" AND table="OUTCOME_STATUS"将只给出该表中每个ID的一行。但是为了安全起见,我在获取histrongtart列值时使用Max()。
那么你可以使用下面的update语句来完成你的工作-
UPDATE Table_Name t_1
SET Hist_End = (SELECT MAX(t_2.Hist_Start)
FROM Table_Name t_2
WHERE t_2.Status = 'CANCELLED'
AND t_2."TABLE" = 'OUTCOME_STATUS'
AND t_2.Id = t_1.Id)
WHERE t_1."TABLE" IN ('DOC_STATUS', 'ENTRY_STATUS')
AND t_1.Hist_End IS NULL;