How to delete the data in SQL Server

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Looks like an Access query?!

What is the error message and how do you execute it? In Access or SQL Server?
Information Services Manager
Commented:
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.
Distinguished Expert 2017

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

Do you get a constraint violation?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial