How to delete the data in SQL Server

Hi
I want to delete some data in four tables in SQL server back end but the delete query below is failing to do so kindly check the query below and advise accordingly

DELETE DISTINCTROW tblCustomerInvoice.InvoiceID, tblCustomerInvoice.StatusOne
FROM ((tblCustomerInvoice INNER JOIN tblLineDetails ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID) INNER JOIN tblSalesControls ON tblCustomerInvoice.InvoiceID = tblSalesControls.InvoiceID) INNER JOIN tblCostofSales ON tblCustomerInvoice.InvoiceID = tblCostofSales.InvoiceID
WHERE (((tblCustomerInvoice.InvoiceID)=[Forms]![frmSalesInvoiceDelete]![CboInvoice]) AND ((tblCustomerInvoice.StatusOne) Is Null)) OR (((tblCustomerInvoice.StatusOne)<>"1"));

Regards

Chris

Hopefully you intend to back up that table before performing any delete query. Once you run it there is no undo.

A better method however is to start with a SELECT query... to make sure everything you see in the SELECT is what you want to be deleted.

Also, this looks like an MS ACCESS query, and if that data is in SQL backend, I would not be performing this in MS ACCESS unless it is something that is supposed to be a function in your Access application to be reused.

Select query...For example....
SELECT InvoiceID,* FROM tblCustomerInvoice
INNER JOIN tblLineDetails ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID
INNER JOIN tblSalesControls ON tblCustomerInvoice.InvoiceID = tblSalesControls.InvoiceID
INNER JOIN tblCostofSales ON tblCustomerInvoice.InvoiceID = tblCostofSales.InvoiceID
WHERE (((tblCustomerInvoice.InvoiceID)=[Forms]![frmSalesInvoiceDelete]![CboInvoice]) AND ((tblCustomerInvoice.StatusOne) Is Null)) OR (((tblCustomerInvoice.StatusOne)<>"1"))

Then if you get what you want...

DELETE FROM tblCustomerInvoice WHERE InvoiceID IN(

SELECT InvoiceID FROM tblCustomerInvoice
INNER JOIN tblLineDetails ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID
INNER JOIN tblSalesControls ON tblCustomerInvoice.InvoiceID = tblSalesControls.InvoiceID
INNER JOIN tblCostofSales ON tblCustomerInvoice.InvoiceID = tblCostofSales.InvoiceID
WHERE (((tblCustomerInvoice.InvoiceID)=[Forms]![frmSalesInvoiceDelete]![CboInvoice]) AND ((tblCustomerInvoice.StatusOne) Is Null)) OR (((tblCustomerInvoice.StatusOne)<>"1"))
)

This is safer because you get to preview what will actually be deleted.

If this function is supposed to be part of your application, I would go with putting the query into a stored procedure on the SQL server and call that procedure with the supplied parameter [Forms]![frmSalesInvoiceDelete]![CboInvoice]) ...and let the rest happen on the server backend.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 41

ste5anSenior DeveloperCommented: 2018-09-20

Looks like an Access query?!

What is the error message and how do you execute it? In Access or SQL Server?

LVL 25

Ron MalmsteadInformation Services ManagerCommented: 2018-09-20

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

LVL 83

arnoldCommented: 2018-09-20

Use selects instead of delete first to confirm the rows impacted are the ones you want deleted.

Do you get a constraint violation?

How to delete the data in SQL Server

Who is Participating?