SQL: Struggling with DELETES

"darrel" <notreal@nowhere.com> wrote in message
news:essif7$2o1$1@forums.macromedia.com...
> DELETE FROM WeFAQsVariations
> WHERE EXISTS (
> SELECT * FROM WeFAQs faqs INNER JOIN
> We_about_categories cats ON faqs.categoryID =
> cats.categoryID
> WHERE (vars.collectionID = faqs.collectionID) AND (faqs.categoryID = 9 OR
> cats.categoryParentID = 9)
> )
>
> Did I come close?
>

That's exactly right if "vars.collectionID" above is just shorthand for the
forum post. :) You should spell out WeFAQsVariations.collectionID.

When you're doing an UPDATE or DELETE, you can't use a table alias for the
target table (well, you can, but things can get messy depending on your
DBMS; the careful ones will just throw an error). MS SQL accepts an alias
*if* the alias is also the target, but that's just to support the UPDATE ...
FROM and DELETE FROM ... FROM T-SQL extensions. If you're using a standard
syntax like you are here, it's best to stick all the way to the standard.

Lionstone:

Have time to help me with another one? ;o)

So, the next challenge is pretty much the same thing, but with a
double-join. The query I use to get the data is such:

SELECT * FROM WeFAQsVariations INNER JOIN
WeFAQs faqs ON vars.collectionID = faqs.collectionID
INNER JOIN
We_about_categories cats ON faqs.categoryID =
cats.categoryID
WHERE (faqs.categoryID = 9) OR (cats.categoryParentID = 9)

I want to delete the records from WeFAQsVariations where the collectionID
matches the colletionID of the record in the FAQs table where the categoryID
is or is a child of '9'

I didn't get very far on this one, though, as the simple delete command:

DELETE FROM WeFAQsVariations
WHERE collectionID = ?

won't work, as I'm going to be returning a bunch of different collectionIDs.
I think I need the EXISTS condition:

DELETE FROM WeFAQsVariations
WHERE EXISTS (
SELECT * FROM WeFAQs faqs INNER JOIN
We_about_categories cats ON faqs.categoryID =
cats.categoryID
WHERE (vars.collectionID = faqs.collectionID) AND (faqs.categoryID = 9 OR
cats.categoryParentID = 9)
)

Did I come close?

_Darrel

> Nope. That will delete from both tables.
> Technically, you have to use a subquery in the WHERE clause.
> DELETE FROM items
> WHERE categoryID=9 OR EXISTS(SELECT * FROM categories WHERE
> parentCategoryID=9 AND categoryID=items.categoryID)

That works! Took me a few minutes to fully understand the EXISTS part, but I
get it now! Thanks!

> In real life, since you use MS SQL, you get to use a T-SQL extension
> similar to the UPDATE ... FROM syntax.
>
> DELETE FROM items
> FROM items I
> INNER JOIN categories C ON I.categoryID=C.categoryID
> WHERE I.categoryID=9 OR C.categoryParentID=9

Aside from the double FROM, I like that syntax a bit better. Thanks for that
throrough answer!

-Darrel

> It looks like you are using a MSSQL DB. If you are there is a built in
> feature
> that will delete all records that correspond to a parent record. It is
> called
> cascading deletes. In your relationships, in the properties panel,
> under
> table designer, there is an option called INSERT and UPDATE specification.
> Under that is a rule for Update and for Delete. For the Delete rule
> choose
> cascade. I would recommend doing the same for the Update rule. Now if
> delete
> anything from a parent table and related records in the child table will
> be
> deleted automatically.

That's what I don't want. I only want to delete records from one table.
HOwever, to figure out the specific records, I need to join the data with
the other table.

-Darrel

It looks like you are using a MSSQL DB. If you are there is a built in feature that will delete all records that correspond to a parent record. It is called cascading deletes. In your relationships, in the properties panel, under table designer, there is an option called INSERT and UPDATE specification. Under that is a rule for Update and for Delete. For the Delete rule choose cascade. I would recommend doing the same for the Update rule. Now if delete anything from a parent table and related records in the child table will be deleted automatically.

This option is also available in an Access database if you happen to be using that DB.

"darrel" <notreal@nowhere.com> wrote in message
news:essd8j$poh$1@forums.macromedia.com...
> Now, what if I want to delete the records from items, but not delete any
> records from categories?
>
> Can I just use?:
>
> DELETE FROM items INNER JOIN
> categories ON items.categoryID =
> categories.categoryID
> WHERE (items.categoryID = 9) OR
> (categories.categoryParentID = 9)
>
> -Darrel

Nope. That will delete from both tables.
Technically, you have to use a subquery in the WHERE clause.
DELETE FROM items
WHERE categoryID=9 OR EXISTS(SELECT * FROM categories WHERE
parentCategoryID=9 AND categoryID=items.categoryID)

In real life, since you use MS SQL, you get to use a T-SQL extension similar
to the UPDATE ... FROM syntax.

DELETE FROM items
FROM items I
INNER JOIN categories C ON I.categoryID=C.categoryID
WHERE I.categoryID=9 OR C.categoryParentID=9

The table aliases above are NOT optional in this one case. The double FROM
is not a typo. It really says DELETE FROM ... FROM.

But it works. :)

> Can I just use?:

After playing with some sample data, no, I can't do that.

So, I guess the main question is how does one delete specific records from a
table where one normally has to use a join to figure out which rows of data
to delete?

-Darrel