SQL Delete Question Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Delete Question Help

Author

Topic

Confused SQL
Starting Member

3 Posts

Posted - 2012-02-22 : 16:52:22

Hi Everyone.
I need to Write a DELETE statement for the Vendor table to Delete the vendors that are located in states from which no vendor has ever sent an invoice. I'm not even sure where to start. Any ideas would be greatly appreciated?

Here are the tables I'm using if that helps

Invoices Table

InvoiceID
VendorID
InvoiceNumber
InvoiceDate
InvoiceTotal
PaymentTotal
CreditTotal
TermsID
InvoiceDueDate
PaymentDate

________________
Vendors table

VendorID
VendorName
VendorAdress1
VendorAdress2
VendorCity
VendorState
VendorZipCode
VendorPhone
VendorContactLName
VendorContactFName
DefaultTermsID
DefaultAccountNo

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-22 : 17:14:46

You can use the following query:

DELETE FROM Vendors
-- SELECT * FROM Vendors
WHERE NOT EXISTS
(
	SELECT * FROM Invoices
	WHERE Invoices.VendorId = Vendors.VendorId
)

Before you run it though, comment the first line, uncomment the second line and run it. That will tell you the rows that are going to be deleted. If you are satisfied comment the second line, uncomment the first line and run it.

Confused SQL
Starting Member

3 Posts

Posted - 2012-02-22 : 18:05:57

Thanks but that doesn't seem to work. It deletes 132 rows. The results I am supposed to get deletes only 32 rows.

The hint that the question gives is to Use a subquery coded with “SELECT DISTINCT VendorState” introduced with the NOT IN operator. if that helps

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-22 : 19:41:14

quote:
Originally posted by Confused SQL

Thanks but that doesn't seem to work. It deletes 132 rows. The results I am supposed to get deletes only 32 rows.

The hint that the question gives is to Use a subquery coded with “SELECT DISTINCT VendorState” introduced with the NOT IN operator. if that helps

Had no idea that that is a homework question. People who run this forum do not look kindly upon people who ask homework questions on the forum and on people who give away the answers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 09:00:14

quote:
Originally posted by Confused SQL

Thanks but that doesn't seem to work. It deletes 132 rows. The results I am supposed to get deletes only 32 rows.

The hint that the question gives is to Use a subquery coded with “SELECT DISTINCT VendorState” introduced with the NOT IN operator. if that helps

you should have serached for usage of DISTINCT and tried for solution yourself. Anyways I think issue seems to be way data exists in tables. Can you give some sample data from tableS?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources