CODEWITHSUNDEEP
sqlmysqldatabase
James Maroney
James Maroney

Reputation: 3256

Cleaning up database records

I have a mysql table which contains versionable key/value pairs. It has columns:

Id(int,autoincrement,primary key) Key(varchar) Value(text) Version(int)

As the value for a given key is updated, the version number is incremented and a new row is added, rather than altering the existing entry. Now, this system has gone through a heavy editing phase and there are tons of old versions that are useless to me.

I haven't had any luck trying to construct a SQL script that will remove all but the hightest version for each key in the table. I would much appreciate anyone's help.

Upvotes: 2

Views: 138

Answers (3)

FeRtoll
FeRtoll

Reputation: 1267

delete * from yourtable where id not in (select id from yourtable x where version=(select max(version) from yourtable where id=x.id))

not shure but should work! :)

try first with select like this:

select * from yourtable where id not in (select id from yourtable x where version=(select max(version) from yourtable where id=x.id)) limit 500

Upvotes: 0

Jason
Jason

Reputation: 15358

Test oout this select script, it should select all the old versions of the rows.

SELECT * FROM table o where id in (SELECT i.* FROM table i WHERE i.Version<>MAX(i.Version) and i.ID=o.ID )

If it works, then it's just a matter of replacing "SELECT *" with "DELETE". Please make sure you back up the table before you delete anything.

http://www.cyberdesignworks.com.au/news-blog/web-developers-MySQL-Quick-Tips (See copy table through SQL)

My script makes no assumptions about the highest id being the highest version.

Upvotes: 0

Paul Creasey
Paul Creasey

Reputation: 28824

Makes the hopefully safe assumption that the highest version also has the highest id.

delete from table
where id not in
    (select max(id)
    from table
    group by key)

Upvotes: 1

Related Questions