Solved

DELETE Query on MSSQL 6.5?

Posted on 1998-08-08
11
227 Views
Last Modified: 2010-03-19
I create my SQL statements in "MS Access" and have sofar been able to run them on our MSSQL 6.5 server by copying the SQL statement.
But when i create a "DELETE" query, I cant seem to do that.

An example SQL statement:

DELETE Customers.ID, Orders.*
FROM Customers INNER JOIN Orders ON Customers.ID = Order.CustomerID
WHERE (((Customers.ID)=1));

The SQL 6.5 generates the following error:

Msg 170, Level 15, State 1
Line 1: Incorrect syntax near ','.
Msg 170, Level 15, State 1
Line 3: Incorrect syntax near 'Customers'.


What is the correct SQL for this delete query?


Thanks in advance
0
Comment
Question by:cisDK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
11 Comments
 

Author Comment

by:cisDK
ID: 1089494
Edited text of question
0
 

Author Comment

by:cisDK
ID: 1089495
Adjusted points to 214
0
 
LVL 1

Expert Comment

by:BlackAndWhite
ID: 1089496
You cannot delete specific column values.

Deletion can be done only in rows. The statement would then be
DELETE  
FROM Orders
WHERE CustomerID =1

And If you wanted to delete the Customer Record too,

DELETE  
FROM Customers
WHERE ID =1

one more thing,
Notice that you say Order.CustomerId instead of Orders.CustomerId
just before the WHERE.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:cisDK
ID: 1089497
Thank you for your proposed answer Black&White, you have proved that my example was a poor one! Sorry, i will try again:

My (typical) scenario:
=======================================
1) I have some customers              (Table: Customers)
2) Each customer have some orders     (Table: Orders)
3) Each order have some products      (Table: OrderedProducts)

----------------------------------------
What I want to do is:
I want to delete customer with CustomerID "15"


----------------------------------------
To do this I figure that i must
1) First delete all products from all his/her orders.
2) Second, delete all his orders
3) Third, delete the customer


----------------------------------------
My question is then:

How would I write an SQL statement that deleted all orders products from the customer with ID 15?

0
 
LVL 2

Accepted Solution

by:
percosolator earned 210 total points
ID: 1089498
Unless you have enforced a cascade delete on the SQL server via delete triggers you won't be able to delete from three tables with one SQL statement.

Your are correct in your diagnosis of what you are going to have to do.  The syntax for deleting all products for customer 15 is not immediately obvious.

SQL Statements:

DELETE
FROM
     OrderedProducts
FROM
     Orders
WHERE
     Orders.OrderID = OrderedProduct.OrderID
AND
     Orders.CustomerID = 15


DELETE
FROM
     Orders
WHERE
     CustomerID = 15


DELETE
FROM
     Customers
WHERE
     CustomerID = 15


------
0
 
LVL 1

Expert Comment

by:BlackAndWhite
ID: 1089499
You Question was not very obvious to me...Now it is clear.

As Percosolator suggests, You have to do it with triggers.  Write A Delete trigger on Customer that would first delete the relevent records and then delete the customer record itself. Then you could have just the statment to delete the customer record and wiping out his details would be taken care of by the trigger.

Just curious: Do clients want to wipe out order records if the customer is no longer a customer?  Should you not have an Active or Invactive bit to do it.  I am assuming that you did it just for the sake of an example.
0
 
LVL 2

Expert Comment

by:percosolator
ID: 1089500
Just wanted to inform you that I forgot to put an "s" on a line

-------------------------------------

WHERE
     Orders.OrderID = OrderedProduct.OrderID

--should be--

WHERE
     Orders.OrderID = OrderedProducts.OrderID

----------------------------
0
 

Author Comment

by:cisDK
ID: 1089501
Thank you all!

Black&White: Yes, just an example
0
 

Author Comment

by:cisDK
ID: 1089502
Just a comment after i figured, thanks to you, out how to use proper delete sql statements on our sql-server.

Why does Access generate SQL as it does, when the SQL-Server syntax is much easire to read and understand?
As I see it, the Access way also leaves some room for errors, while the SQL-server doesnt!?

I mean, DELETE Orders.*, Products.SkemaiD .... etc, looks like it deletes Products.SKemaID aswell, but it only deleted the record from Orders...

Just wondering after my problem has been solved...

Have fun!!!


0
 
LVL 1

Expert Comment

by:BlackAndWhite
ID: 1089503
SQL Server is ANSI standard compliant.  Access is more flexible ...you can do a lot more things with Access SQL than you can with SQL Server's SQL but it cannot be ported across several DBMS's.  However you can port the SQL of SQL Server to any ANSI compliant DBMS.
0
 
LVL 2

Expert Comment

by:percosolator
ID: 1089504
beat me to the punch, BlackAndWhite.  Hadn't had a chance to respond.

cisDK:  that's pretty much how I would have answered, Access allows you ability to do many things that SQL can't, like drop columns and change physical layouts without dropping the table (really would like to be able to do that). :)

0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question