Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

DELETE Query on MSSQL 6.5?

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
cisDK
Asked:
cisDK
  • 5
  • 3
  • 3
1 Solution
 
cisDKAuthor Commented:
Edited text of question
0
 
cisDKAuthor Commented:
Adjusted points to 214
0
 
BlackAndWhiteCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
cisDKAuthor Commented:
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
 
percosolatorCommented:
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
 
BlackAndWhiteCommented:
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
 
percosolatorCommented:
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
 
cisDKAuthor Commented:
Thank you all!

Black&White: Yes, just an example
0
 
cisDKAuthor Commented:
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
 
BlackAndWhiteCommented:
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
 
percosolatorCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now