Solved

DELETE Query on MSSQL 6.5?

Posted on 1998-08-08
11
223 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

789 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