FB-3 Delete

[Henry Olive 5]

Good Morning,
 

DELETE FROM ORDETAIL OD 
JOIN ORDERS O  ON  O.ID = OD.ID
WHERE OD.REMAINQTY = 0  and  O.INVDOCNO IS NOT NULL

 

What is the correct SQL code of my above wrong SQL code ?

 

Thank You

 

 

[Stano 143]

You are just like me. You are giving insufficient data, information.
First, a supplementary question: do you want to first delete the records in the Detail table and then in the Master?
Because if so, then I think you don't understand relational databases. I am convinced that you should deal with it in the definition of the table.
You have a foreign key there. There should be DELETE CASCADE.

Edited August 14, 2022 by Stano

[Henry Olive 5]

Thank You Stano

I dont have foreign key,

First i want to delete detail record if the condition is met

then i'll delete master record like below

Delete From Orders O Where Not Exists (Select * from OrDetail OD Where O.ID=OD.ID )

Edited August 14, 2022 by Henry Olive

[Stano 143]

What is this: JOIN ORDERS O ON O.ID = OD.ID
You did not show us the definitions of the tables. If you don't have a foreign key in the Detail tab, you have a bad DB design. Redo it.
No, you have to delete the Master record, and DB will take care of deleting all linked records in Detail.

[Henry Olive 5]

Thank You Stano

I change my question  How to delete a detail table record

according to both Master & Detail tables's some field values ?

For example 

DELETE FROM ORDETAIL OD   WHERE OD.REMAINQTY=0

Above code works  but i also want to add into above code an additional condition from Master Table 

which is something like    AND ORDERS.INVDOCNO IS NOT NULL

One condition from Detail table second condition comes from Master Table

If both conditions occur  then i'll delete detail record.

 

[Stano 143]

So in the WHERE section use EXISTS and there the appropriate SELECT.
I "admire" people like you. You won't read the help, you won't give us the information we need and you'll insist on your own! If you were close at hand, you would have already gotten an earful

[Serge_G 87]

Agree with @Stano. Read the manuals https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-dml-delete

Have a look at global syntax of Delete it does not support jointures (noted as [<joins>] in a SELECT clause).

You have to use a subquery

 

DELETE FROM ORDETAIL OD WHERE OD.REMAINQTY = 0
AND Not Exists (SELECT 1 FOM ORDERS WHERE ID = OD.ID  and INVDOCNO IS NOT NULL ) 

 

 

 

[Henry Olive 5]

Thank you SO MUCH  Serge