Jump to content
Henry Olive

FB-3 Delete

Recommended Posts

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

 

 

Share this post


Link to post

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 by Stano

Share this post


Link to post

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 by Henry Olive

Share this post


Link to post

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.

Share this post


Link to post

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.

 

Share this post


Link to post

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:classic_biggrin:

  • Like 1

Share this post


Link to post

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 ) 

 

 

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×