Re: cannot delete

It looks like fwdinvoice_det.invoiceno is primary key in fwdinvoice_det_at_oracle8 table, therefore the error raised by the first query.

Cheers,
Valentin

skyloon wrote:
> there are 2 query which will return 0 record when I just select it from
> table.
> but 2 queries are almost same structure but 1 return error and another
> 1 no error.
> anybody know why?
>
> DELETE FROM
> (SELECT
> FWDBOOK_HBL.COMPANYCODE,FWDBOOK_HBL.BRANCHCODE,FWDBOOK_HBL.JOBNO
> FROM FWDBOOK_HBL_at_ORACLE8,FWDBOOK_HD_at_ORACLE8
> WHERE FWDBOOK_HD.COMPANYCODE = FWDBOOK_HBL.COMPANYCODE
> AND FWDBOOK_HD.BRANCHCODE=FWDBOOK_HBL.BRANCHCODE
> AND FWDBOOK_HD.JOBNO=FWDBOOK_HBL.JOBNO
> AND (FWDBOOK_HD.CANCEL='Y' OR FWDBOOK_HD.CLSDBO='Y')
> AND FWDBOOK_HD.BOOKINGDATE BETWEEN TO_DATE('12-12-2001','DD-MM-YYYY')
> AND TO_DATE('12-12-2006','DD-MM-YYYY'));
>
> ERROR at line 2:
> ORA-01752: cannot delete from view without exactly one key-preserved
> table
>
>
> DELETE FROM
> (SELECT FWDINVOICE_DET.COMPANYCODE,FWDINVOICE_DET.BRANCHCODE,
> FWDINVOICE_DET.JOBNO,FWDINVOICE_DET.INVOICENO
> FROM FWDINVOICE_DET_at_ORACLE8,FWDINVOICE_HD_at_ORACLE8
> WHERE FWDINVOICE_HD.COMPANYCODE = FWDINVOICE_DET.COMPANYCODE
> AND FWDINVOICE_HD.BRANCHCODE=FWDINVOICE_DET.BRANCHCODE
> AND FWDINVOICE_HD.INVOICENO=FWDINVOICE_DET.INVOICENO
> AND ( FWDINVOICE_HD.CANCEL= 'Y' OR FWDINVOICE_HD.APPROVED='Y')
> AND FWDINVOICE_HD.INVOICEDATE BETWEEN
> TO_DATE('12-12-2001','DD-MM-YYYY')
> AND TO_DATE('12-12-2006','DD-MM-YYYY'));
>
> 0 rows deleted.
Received on Tue Dec 12 2006 - 11:04:27 CST