Professional Web Applications Themes

unambiguous delete from self-join? - MySQL

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 ...

  1. #1

    Default unambiguous delete from self-join?

    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 Guest

  2. #2

    Default Re: unambiguous delete from self-join?

    Jason S wrote: 

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


    Paul Guest

  3. #3

    Default Re: unambiguous delete from self-join?

    On Apr 28, 1:25 pm, "Paul Lautman" <com>
    wrote: 

    ===
    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.


    Jason Guest

Similar Threads

  1. Delete with a multi-column join?
    By leon-pg@comvision.com in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 25th, 10:03 PM
  2. Replies: 2
    Last Post: September 18th, 09:59 PM
  3. outer join, cross join, union? DDL/sample data incl.
    By Kevin in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:01 PM
  4. Rewrite Query using Inner Join, Left Join e.t.c
    By Vishal in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 23rd, 08:02 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139