Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2014
    Posts
    1

    Unanswered: delete entries in mysql

    Hi
    I have a question on appending and deleting entries in mysql table.

    This is my sample table.
    table name: details
    Code:
    Code:
    id_name | model | mode | media| first | end | id | level |
    +--------------------+-------+---------+-----+-------+-------+--------+--------+
    | PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
    | PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 3 | 14033 | 15627 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 3 | 14756 | 15627 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 3 | 14892 | 15627 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 3 | 15304 | 15627 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 3 | 15550 | 15627 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | T0 | 15550 | 16050 | 6255 | q |
    | PSK_30s1189731L001 | -1 | 1 | 3 | 999 | 7531 | 9808 | c |
    | PSK_30s1189731L001 | -1 | 1 | 3 | 999 | 6487 | 9808 | c |
    | PSK_30s1189731L001 | -1 | 1 | 5 | 6609 | 8257 | 9808 | c |
    | PSK_30s1189731L001 | -1 | 1 | 3 | 999 | 6285 | 9808 | c |
    | PSK_30s716971L003 | -1 | 1 | T0 | 23604 | 24104 | 2285 | c |
    | PSK_30s716971L003 | -1 | 1 | 3 | 24070 | 24104 | 2285 | c |
    | PSK_30s716971L003 | -1 | 1 | 3 | 24070 | 27133 | 2285 | c |
    | PSK_30s716971L003 | -1 | 1 | 3 | 24070 | 28318 | 2285 | c |
    | PSK_30s716971L003 | 1 | 1 | N | 24070 | 28326 | 2285 | c |
    | PSK_30s716971L003 | -1 | 1 | 5 | 27036 | 28326 | 2285 | c 
    | PSK_30s716971L003 | -1 | 1 | 5 | 27483 | 28326 | 2285 | c
    I have numerous entries of same id name belonging to same median number.However,I want to only retain the entries having the longest first and end position and discard the remaining entries


    For eg for id name ="PSK_30s1207681L002" AND median = 5 we have four entries
    Code:
    Code:
    id_name | model | mode | media| first | end | id | level |
    +--------------------+-------+---------+-----+-------+-------+--------+--------+
    | PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
    | PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
    but I want to retain only the one having longest first and end points ie

    Code:
    Code:
    Code:
    | PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
    | PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
    Here ,since for id name ="PSK_30s1207681L002" & median = N we have only 1 entry I want retain that one.



    My final output in mysql table should look like this.

    Code:
    id_name | model | mode | media| first | last | id | level |
    | PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
    | PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | 3 | 14033 | 15627 | 6255 | q |
    | PSK_30s1207681L002 | -1 | 1 | T0 | 15550 | 16050 | 6255 | q |
    | PSK_30s1189731L001 | -1 | 1 | 3 | 999 | 7531 | 9808 | c |
    | PSK_30s1189731L001 | -1 | 1 | 5 | 6609 | 8257 | 9808 | c |
    | PSK_30s716971L003 | -1 | 1 | T0 | 23604 | 24104 | 2285 | c |
    | PSK_30s716971L003 | -1 | 1 | 3 | 24070 | 28318 | 2285 | c |
    | PSK_30s716971L003 | 1 | 1 | N | 24070 | 28326 | 2285 | c |
    | PSK_30s716971L003 | -1 | 1 | 5 | 27036 | 28326 | 2285 | c
    Is there anything that I could do to append it?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try the following example.
    (Though, it was not tested on MySQL. It was tested and worked on DB2.)

    Example:
    Code:
    DELETE FROM details AS d
     WHERE EXISTS
           (SELECT 0
             FROM  details AS e
             WHERE e.id_name        = d.id_name
              AND  e.media          = d.media
              AND  e.last - e.first > d.last - d.first 
           )
    ;

Posting Permissions

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