Help with delete statement

tjones1105 · #1 (**permalink**) 11-08-10, 16:11

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

dav1mo · #2 (**permalink**) 11-09-10, 09:26

Code:

delete from table a
where a.date < (select max(b.date) from table b
                where a.client_id = b.client_id)

Dave

tjones1105 · #3 (**permalink**) 11-10-10, 20:07

thanks,
but I cant seem to get it to work...

Quote:

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

r937 · #4 (**permalink**) 11-11-10, 07:11

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 )