Hmm. I've done self-joins with SELECT and with UPDATE, but I haven't
managed to handle it in DELETE & wonder if it is possible with MySQL.

Here's my situation:
I have a very simple table called "item_attr". It has two columns,
both keys. One column is an "item_id" and the other column is an
"attr_id".

Items with attr_id = 0 represent selected items; items with attr_id =
2 represent items in a particular category.

What I want to do is to "remove" all the items in a particular
category (attr_id = 2) that are also selected; more specifically: I
want to eliminate all rows in this table which have attr_id = 2 and
which have an item_id which also appears in this same table with
attr_id = 0.

I can select those items:

select iattr.item_id from item_attr iattr, item_attr isel where
isel.attr_id = 0 and isel.item_id = iattr.item_id and iattr.attr_id =
2

I can change the attr_id for those items:
update item_attr iattr, item_attr isel set iattr.attr_id = 0xffffffff
where isel.attr_id = 0 and isel.item_id = iattr.item_id and
iattr.attr_id = 2

But I can't seem to figure out how to delete them in one swoop. I can
use the previous query to create a "bogus" attr_id value 0xffffffff,
and then delete rows in the table with attr_id = 0xffffffff... but
technically that's not quite what I want, since it fails if there are
rows in the table with attr_id = 0xffffffff that mean something else.

Any thoughts?

Jason S wrote:
> Hmm. I've done self-joins with SELECT and with UPDATE, but I haven't
> managed to handle it in DELETE & wonder if it is possible with MySQL.
>
> Here's my situation:
> I have a very simple table called "item_attr". It has two columns,
> both keys. One column is an "item_id" and the other column is an
> "attr_id".
>
> Items with attr_id = 0 represent selected items; items with attr_id =
> 2 represent items in a particular category.
>
> What I want to do is to "remove" all the items in a particular
> category (attr_id = 2) that are also selected; more specifically: I
> want to eliminate all rows in this table which have attr_id = 2 and
> which have an item_id which also appears in this same table with
> attr_id = 0.
>
> I can select those items:
>
> select iattr.item_id from item_attr iattr, item_attr isel where
> isel.attr_id = 0 and isel.item_id = iattr.item_id and iattr.attr_id =
> 2
>
> I can change the attr_id for those items:
> update item_attr iattr, item_attr isel set iattr.attr_id = 0xffffffff
> where isel.attr_id = 0 and isel.item_id = iattr.item_id and
> iattr.attr_id = 2
>
> But I can't seem to figure out how to delete them in one swoop. I can
> use the previous query to create a "bogus" attr_id value 0xffffffff,
> and then delete rows in the table with attr_id = 0xffffffff... but
> technically that's not quite what I want, since it fails if there are
> rows in the table with attr_id = 0xffffffff that mean something else.
>
> Any thoughts?

My first thought the Multi-Table Delete syntax on the page:
http://dev.mysql.com/doc/refman/5.0/en/delete.html

On Apr 28, 1:25 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> My first thought the Multi-Table Delete syntax on the page:http://dev.mysql.com/doc/refman/5.0/en/delete.html

===
Note: If you provide an alias for a table, you must use the alias
when referring to the table:

DELETE t1 FROM test AS t1, test2 WHERE ...
===

Oh -- I didn't realize you could use aliases in a delete statement.