Solved

DELETE Query on MSSQL 6.5?

Posted on 1998-08-08
11
219 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
  • 5
  • 3
  • 3
11 Comments
 

Author Comment

by:cisDK
Comment Utility
Edited text of question
0
 

Author Comment

by:cisDK
Comment Utility
Adjusted points to 214
0
 
LVL 1

Expert Comment

by:BlackAndWhite
Comment Utility
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
 

Author Comment

by:cisDK
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Expert Comment

by:BlackAndWhite
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you all!

Black&White: Yes, just an example
0
 

Author Comment

by:cisDK
Comment Utility
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
Comment Utility
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
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now