SQL Server delete query involving two tables

Question

So I have tables A and B in SQL Server, and columns a and b respectively. I want to do the following in pseudo-query command, but I can't seem to figure it out.

I want to

DELETE FROM A 
WHERE a < 100 "and only if these selected (for deletion) values don't exist in column b in table B"

The reason is that I'm trying to delete some data from table A, but it is giving me an error saying that there is a constraint between values in A.a and B.b .

Does this involve aliases? It is confusing..

What version of SQL Server are you using?

Abe Miessler
– Abe Miessler

2011-08-12 22:34:44 +00:00
Commented Aug 12, 2011 at 22:34 — Abe Miessler
– Abe Miessler, Commented Aug 12, 2011 at 22:34

Abe Miessler · Accepted Answer · 2011-08-12 22:23:57Z

11

Try this if you are using SQL Server 2005 or newer:

DELETE FROM TableA
WHERE a < 100 AND 
a NOT IN (SELECT B FROM TableB)

For SQL Server 2000 this should work:

DELETE ta
FROM TableA as ta
LEFT JOIN TableB as tb
ON ta.a = tb.b
WHERE ta.a < 100 AND  tb.b IS NULL

edited Aug 12, 2011 at 22:23

answered Aug 12, 2011 at 22:17

Abe Miessler

85.7k104 gold badges323 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

shelleybutterfly Over a year ago

I wasn't sure from the question, but given that there were constraint violations, might TableB.b be a foreign key lookup into TableA? If so, would there be any difference in the form of the delete other than substituting 'id' for 'a' in the appropriate clause?

Abe Miessler Over a year ago

Hmmm, i'm not sure I understand. I agree on your foreign key assumption. This delete would basically remove any records from TableA that were not being referenced from TableB, which appears to be what he/she needs.

laketuna Over a year ago

@Abe Miessler, your first query worked just great! It looks like in MSSQL you can enforce a bunch of restrictions..? I've learning MySQL lately, but there doesn't seem to be any of these features I see in the SQL Server Management Studio.

Abe Miessler Over a year ago

Yes, you are correct, SQL Server does have the ability to enforce data integrity. While I am not as well versed in MySQL, I am fairly certain that you can enforce referential integrity. Take a look at these links: databasejournal.com/features/mysql/article.php/2248101/…, dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html

shelleybutterfly Over a year ago

@Abe Hmm, well, I could just be confused. Let's take the first command. In my head I translate the first into a command like:<br/>"ForAll (ARec in TableA) Where (ARec.a < 100) And (ForAll BRec in TableB Not (ARec.a Equals BRec.B))"<br/>In other words, it looks to me as if what's being compared is the numeric field in each ARec.a to what would need to be also be a numeric field in BRec; BRec.b.<br/>As for the second, very similar thing; you're joining on ta.a = tb.b so if ta.a is a numeric field, it seems like ta.b also would have to be numeric.

|

Collectives™ on Stack Overflow

SQL Server delete query involving two tables

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related