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
LVL 3
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.ScDirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ste5anSenior DeveloperCommented:
Looks like an Access query?!

What is the error message and how do you execute it? In Access or SQL Server?
0
Ron MalmsteadInformation Services ManagerCommented:
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.
1

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
arnoldCommented:
Use selects instead of delete first to confirm the rows impacted are the ones you want deleted.

Do you get a constraint violation?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.