SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    delete based on second row in same table

    I have a situation where I have a two field table, id and value. I want to delete a row if there is a value with the same key and a particular second value.

    like
    id, val
    1, val1
    1, val2

    delete val 1 from the table when val2 exists with the same key.

    I tried this (and a bunch of other combinations) and none seem to work

    delete from table t1 where t1.val = 'val1' and t1.id in (select id from table t2 where t2.val = 'val2')

    This format actually works for the select statement, i.e. replace delete with select *.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What happens when you run that delete query? An error? If so, what error?

    And a question: how comes you have duplicate id's in that table? An error, or are they supposed to be not unique?

  3. #3
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I am running it in phpmyadmin and it gives me a syntax error. #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

    It's actually a lookup table so the id I am referring to is the id of another table so the unique key is the id and the value allowing multiple values per id, but they must be unique.

  4. #4
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To clarify further, I am trying to change all val1 to val2 so any id that has both won't let me make the change. So in those cases I need to delete val1 rather than change it, then I can run the update query to change those that don't already have a val2.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Yes, I understand.
    I looked it up in the manual:
    Currently, you cannot delete from a table and select from the same table in a subquery.
    In PHP I'd do two queries: one to select the id's with value 2, one to delete the rows with the id's found and value 1.

  6. #6
    SitePoint Evangelist
    Join Date
    Nov 2009
    Posts
    471
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I'll do it in PHP. Thanks for the input, I appreciate it.


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
  •