Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    21

    Unanswered: Help with delete statement

    Hello,
    I have a table full of client inventory data and there is a lot of duplicate data from older inserts I would like to remove. I have not done very many complex remove statements so I could use some help.

    Table
    my_inv_data
    Cols
    rid (autoincr, id)
    client_id
    date
    name
    path


    What I'm looking to do is just keep records which match the MAX(`date`) for each client_id.

    Thanks,
    tom

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Code:
    delete from table a
    where a.date < (select max(b.date) from table b
                    where a.client_id = b.client_id)
    Dave

  3. #3
    Join Date
    Dec 2007
    Posts
    21
    thanks,
    but I cant seem to get it to work...

    error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where a.date < ( select MAX(b.date) from my_inv_data b where a. client_id = b.' at line 2

    tom

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think the alias in the outer query is wrong

    try like this --
    Code:
    DELETE 
      FROM my_inv_data 
     WHERE date < ( SELECT MAX(b.date) 
                      FROM my_inv_data AS foo
                     WHERE foo.client_id = daTable.client_id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2007
    Posts
    21
    Thanks,
    but now I get this error...

    Error Code: 1093
    You can't specify target table 'my_inv_data' for update in FROM clause
    tom

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    seems like you can't select and delete from the same table

    try creating a temproray table with the MAXes in it, then DELETE WHERE NOT EXISTS...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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