UPDATE SCONTAINERINFO SC
SET (SC.SPCI_FULL_IN_TIME, SC.REC_VER) =
(SELECT T1.ICEO_GATE_IN_OUT_TIME, SC.REC_VER + 1
FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE T1.ICEO_BATCH_NO = '3358749'
AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID
AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID
AND (SHSS.SHSS_ETD_TIME + 5 >= T1.ICEO_GATE_IN_OUT_TIME OR
SHSS.SHSS_ETD_TIME - 5 <= T1.ICEO_GATE_IN_OUT_TIME)
AND SHSS.SHSS_ETD_TIME < SYSDATE + 30
AND SC.SPCI_FULL_IN_TIME IS NULL
AND SSEM.SSEM_VALID_FLAG = 'Y'
AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO
AND T1.ICEO_DISPOSE_FLAG = 'N'
AND (T1.ICEO_CTN_MOVE_TYPE = 'FI' --重箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ICY' --进口重柜卸驳船
OR T1.ICEO_CTN_MOVE_TYPE = 'FD' --重箱卸船
OR T1.ICEO_CTN_MOVE_TYPE = 'EI' --空箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ED' --空箱卸船
)
AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID
AND SHSS.SHSS_ORG_ID = '441'
AND ROWNUM = 1)
WHERE SC.SPCI_CREATE_TIME > SYSDATE - 30
AND SC.SPCI_ORG_ID = '441'
AND EXISTS
(SELECT 1
FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE T1.ICEO_BATCH_NO = '3358749'
AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID
AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID
AND (SHSS.SHSS_ETD_TIME + 5 >= T1.ICEO_GATE_IN_OUT_TIME OR
SHSS.SHSS_ETD_TIME - 5 <= T1.ICEO_GATE_IN_OUT_TIME)
AND SHSS.SHSS_ETD_TIME < SYSDATE + 30
AND SC.SPCI_FULL_IN_TIME IS NULL
AND SSEM.SSEM_VALID_FLAG = 'Y'
AND T1.ICEO_DISPOSE_FLAG = 'N'
AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO
AND (T1.ICEO_CTN_MOVE_TYPE = 'FI' --重箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ICY' --进口重柜卸驳船
OR T1.ICEO_CTN_MOVE_TYPE = 'FD' --重箱卸船
OR T1.ICEO_CTN_MOVE_TYPE = 'EI' --空箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ED' --空箱卸船
)
AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID
AND SHSS.SHSS_ORG_ID = '441')
SET (SC.SPCI_FULL_IN_TIME, SC.REC_VER) =
(SELECT T1.ICEO_GATE_IN_OUT_TIME, SC.REC_VER + 1
FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE T1.ICEO_BATCH_NO = '3358749'
AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID
AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID
AND (SHSS.SHSS_ETD_TIME + 5 >= T1.ICEO_GATE_IN_OUT_TIME OR
SHSS.SHSS_ETD_TIME - 5 <= T1.ICEO_GATE_IN_OUT_TIME)
AND SHSS.SHSS_ETD_TIME < SYSDATE + 30
AND SC.SPCI_FULL_IN_TIME IS NULL
AND SSEM.SSEM_VALID_FLAG = 'Y'
AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO
AND T1.ICEO_DISPOSE_FLAG = 'N'
AND (T1.ICEO_CTN_MOVE_TYPE = 'FI' --重箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ICY' --进口重柜卸驳船
OR T1.ICEO_CTN_MOVE_TYPE = 'FD' --重箱卸船
OR T1.ICEO_CTN_MOVE_TYPE = 'EI' --空箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ED' --空箱卸船
)
AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID
AND SHSS.SHSS_ORG_ID = '441'
AND ROWNUM = 1)
WHERE SC.SPCI_CREATE_TIME > SYSDATE - 30
AND SC.SPCI_ORG_ID = '441'
AND EXISTS
(SELECT 1
FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE T1.ICEO_BATCH_NO = '3358749'
AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID
AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID
AND (SHSS.SHSS_ETD_TIME + 5 >= T1.ICEO_GATE_IN_OUT_TIME OR
SHSS.SHSS_ETD_TIME - 5 <= T1.ICEO_GATE_IN_OUT_TIME)
AND SHSS.SHSS_ETD_TIME < SYSDATE + 30
AND SC.SPCI_FULL_IN_TIME IS NULL
AND SSEM.SSEM_VALID_FLAG = 'Y'
AND T1.ICEO_DISPOSE_FLAG = 'N'
AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO
AND (T1.ICEO_CTN_MOVE_TYPE = 'FI' --重箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ICY' --进口重柜卸驳船
OR T1.ICEO_CTN_MOVE_TYPE = 'FD' --重箱卸船
OR T1.ICEO_CTN_MOVE_TYPE = 'EI' --空箱进场
OR T1.ICEO_CTN_MOVE_TYPE = 'ED' --空箱卸船
)
AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID
AND SHSS.SHSS_ORG_ID = '441')
解决方案 »
- 如果建立一个oracle数据库测试机?
- 确认下:是不是真的没有查询表被访问的次数的方法
- Oracle中union问题
- 求 Oracle 入门书籍
- 高分求助!买了个IBM system x3650 服务器,安装的2003server操作系统,安装orcle8.1.6时出错
- 菜鸟请教access数据库导入 oracle数据库问题!!!!!!!!!!!!!
- Oracle中加:号的变量怎么声明?和其它变量有啥区别?
- 在线等待,数据导入导出,急急急!!!
- 冷备的数据文件怎么恢复?
- 如何知道一句SQL语句需要执行多久?
- 求助,服务和监听正常启动,sqlplus却进不去,提示12560错误。
- 执行alter database open卡住应该去哪查看问题原因?
2、把需要更新部分创建一个表,创建索引
3、把更新来源数据创建一个表,创建索引
4、把1、2、3步骤的数据来做更新,既得到更新后的数据,再替换或更新回原表
SET (SC.SPCI_FULL_IN_TIME, SC.REC_VER) =
(SELECT T1.ICEO_GATE_IN_OUT_TIME, SC.REC_VER + 1
FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE T1.ICEO_BATCH_NO = '3358749'
AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID
AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID
AND SHSS.SHSS_ETD_TIME BETWEEN T1.ICEO_GATE_IN_OUT_TIME - 5 AND T1.ICEO_GATE_IN_OUT_TIME + 5
AND SHSS.SHSS_ETD_TIME < SYSDATE + 30
AND SC.SPCI_FULL_IN_TIME IS NULL
AND SSEM.SSEM_VALID_FLAG = 'Y'
AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO
AND T1.ICEO_DISPOSE_FLAG = 'N'
AND T1.ICEO_CTN_MOVE_TYPE IN ('FI','ICY','FD','EI','ED')
AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID
AND SHSS.SHSS_ORG_ID = '441'
AND T1.ICEO_GATE_IN_OUT_TIME IS NOT NULL
AND SC.REC_VER IS NOT NULL
AND ROWNUM = 1)
WHERE SC.SPCI_CREATE_TIME > SYSDATE - 30
AND SC.SPCI_ORG_ID = '441' ;
--SET (SC.SPCI_FULL_IN_TIME, SC.REC_VER) =
using (SELECT T1.ICEO_GATE_IN_OUT_TIME,
T1.ICEO_CONTAINER_NO,
SSEM.SSEM_EXP_BL_ID,
ROW_NUMBER() OVER(PARTITION BY T1.ICEO_CONTAINER_NO,SSEM.SSEM_EXP_BL_ID) AS RN
FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE T1.ICEO_BATCH_NO = '3358749'
AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID
--AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID
AND (SHSS.SHSS_ETD_TIME + 5 >= T1.ICEO_GATE_IN_OUT_TIME OR
SHSS.SHSS_ETD_TIME - 5 <= T1.ICEO_GATE_IN_OUT_TIME)
AND SHSS.SHSS_ETD_TIME < SYSDATE + 30
--AND SC.SPCI_FULL_IN_TIME IS NULL
AND SSEM.SSEM_VALID_FLAG = 'Y'
--AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO
AND T1.ICEO_DISPOSE_FLAG = 'N'
AND T1.ICEO_CTN_MOVE_TYPE in( 'FI','ICY','FD','EI','ED') --重箱进场\进口重柜卸驳船\重箱卸船\空箱进场\空箱卸船
AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID
AND SHSS.SHSS_ORG_ID = '441') T
ON(SC.SPCI_EXP_BL_ID = T.SSEM_EXP_BL_ID AND T.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO AND RN = 1)
WHERE SC.SPCI_CREATE_TIME > SYSDATE - 30
AND SC.SPCI_ORG_ID = '441'
AND EXISTS
AND SC.SPCI_FULL_IN_TIME IS NULL
--SET (SC.SPCI_FULL_IN_TIME, SC.REC_VER) =
using (SELECT T1.ICEO_GATE_IN_OUT_TIME,
T1.ICEO_CONTAINER_NO,
SSEM.SSEM_EXP_BL_ID,
ROW_NUMBER() OVER(PARTITION BY T1.ICEO_CONTAINER_NO,SSEM.SSEM_EXP_BL_ID) AS RN
FROM ICODECO T1, SEXPORTMANIFEST SSEM, SHSAILINGSCHEDULE SHSS
WHERE T1.ICEO_BATCH_NO = '3358749'
AND SSEM.SSEM_VOYAGE_ID = SHSS.SHSS_VOYAGE_ID
--AND SC.SPCI_EXP_BL_ID = SSEM.SSEM_EXP_BL_ID
AND (SHSS.SHSS_ETD_TIME + 5 >= T1.ICEO_GATE_IN_OUT_TIME OR
SHSS.SHSS_ETD_TIME - 5 <= T1.ICEO_GATE_IN_OUT_TIME)
AND SHSS.SHSS_ETD_TIME < SYSDATE + 30
--AND SC.SPCI_FULL_IN_TIME IS NULL
AND SSEM.SSEM_VALID_FLAG = 'Y'
--AND T1.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO
AND T1.ICEO_DISPOSE_FLAG = 'N'
AND T1.ICEO_CTN_MOVE_TYPE in( 'FI','ICY','FD','EI','ED') --重箱进场\进口重柜卸驳船\重箱卸船\空箱进场\空箱卸船
AND SHSS.SHSS_ORG_ID = T1.ICEO_ORG_ID
AND SHSS.SHSS_ORG_ID = '441') T
ON(SC.SPCI_EXP_BL_ID = T.SSEM_EXP_BL_ID AND T.ICEO_CONTAINER_NO = SC.SPCI_CNT_NO AND RN = 1)
WHEN MATCHED THEN
UPDATE SET SC.SPCI_FULL_IN_TIME = ICEO_GATE_IN_OUT_TIME,
SC.REC_VER = SC.REC_VER + 1
WHERE SC.SPCI_CREATE_TIME > SYSDATE - 30
AND SC.SPCI_ORG_ID = '441'
AND EXISTS
AND SC.SPCI_FULL_IN_TIME IS NULL;之前的忘了更新了,用这个看看