版本 ORCALE10DELETE
FROM
SHIPMENT_INFO_ITEM_DETAIL_TBL
INNER JOIN SHIPMENT_INFO_TBL
ON SHIPMENT_INFO_ITEM_DETAIL_TBL.INVOICE_NO = SHIPMENT_INFO_TBL.INVOICE_NO
AND SHIPMENT_INFO_ITEM_DETAIL_TBL.SHIP_DATE = SHIPMENT_INFO_TBL.SHIP_DATE
WHERE
SHIPMENT_INFO_TBL.CLOSE_DATE = '20080509' 上面是SQL SERVER上的,
为什么放在ORACLE下说是语法错误...
FROM
SHIPMENT_INFO_ITEM_DETAIL_TBL
INNER JOIN SHIPMENT_INFO_TBL
ON SHIPMENT_INFO_ITEM_DETAIL_TBL.INVOICE_NO = SHIPMENT_INFO_TBL.INVOICE_NO
AND SHIPMENT_INFO_ITEM_DETAIL_TBL.SHIP_DATE = SHIPMENT_INFO_TBL.SHIP_DATE
WHERE
SHIPMENT_INFO_TBL.CLOSE_DATE = '20080509' 上面是SQL SERVER上的,
为什么放在ORACLE下说是语法错误...
我试了下,是 inner不行,,那就是不能这么写了。。
直接delete 表名
语法要改一下
和不加FROM一样
都可以正常删除表
DELETE shipment_info_item_detail_tbl a
WHERE EXISTS (
SELECT 1
FROM shipment_info_tbl b
WHERE s.invoice_no = b.invoice_no
AND a.ship_date = b.ship_date
AND b.close_date = '20080509')
DELETE shipment_info_item_detail_tbl a
WHERE EXISTS (
SELECT 1
FROM shipment_info_tbl b
WHERE s.invoice_no = b.invoice_no
AND a.ship_date = b.ship_date
AND b.close_date = '20080509')
改后的,语法通过,高人看看业务上有错吗?
DELETE
FROM
SHIPMENT_INFO_ITEM_DETAIL_TBL
WHERE EXISTS
(
SELECT 1
FROM
SHIPMENT_INFO_ITEM_DETAIL_TBL
INNER JOIN
SHIPMENT_INFO_TBL
ON SHIPMENT_INFO_ITEM_DETAIL_TBL.INVOICE_NO = SHIPMENT_INFO_TBL.INVOICE_NO
AND SHIPMENT_INFO_ITEM_DETAIL_TBL.SHIP_DATE = SHIPMENT_INFO_TBL.SHIP_DATE
WHERE
SHIPMENT_INFO_TBL.IN_CLOSE_NO = '030201'
AND SHIPMENT_INFO_TBL.CLOSE_DATE = '20080509'
AND SHIPMENT_INFO_TBL.SHIP_CD = '013'
)
还有个问题 ORACLE10 是不能给表起别名的,是这样吗?
DELETE
FROM
SHIPMENT_INFO_ITEM_DETAIL_TBL
WHERE EXISTS
(
SELECT 1
FROM
SHIPMENT_INFO_ITEM_DETAIL_TBL
INNER JOIN
SHIPMENT_INFO_TBL
ON SHIPMENT_INFO_ITEM_DETAIL_TBL.INVOICE_NO = SHIPMENT_INFO_TBL.INVOICE_NO
AND SHIPMENT_INFO_ITEM_DETAIL_TBL.SHIP_DATE = SHIPMENT_INFO_TBL.SHIP_DATE
WHERE
SHIPMENT_INFO_TBL.CLOSE_DATE = '20080509'
)
表名不能用as 直接重命名就行了