mysql - How to delete records from database table excluding records from another SQL -

mysql - How to delete records from database table excluding records from another SQL -

i've search log table, maintain search logs. table construction searchstring, date, number of results of each search string results , other info. have next sql records need. keyword searched several times latest date important. utilize next sql records need. it's working fine.

select id, searchstring, max(logdate) logdate log_search locale = 'en' , results > 0 grouping searchstring order logdate desc

my problem there millions of records , need clean up. want maintain records matches sql above.

i tried utilize not in on id field since sorting logdate of import didn't allow me.

delete log_search id not in (...myquery...) gives operand should contain 1 column(s) error

another of import field locale . while deleting records en need maintain other locales though don't match sql above.

is there way delete records , maintain ones need.

edit

table construction id - auto increment searchstring results - containt number of results searchstring logdate - date , time search made

results , logdate both of import latest query homecoming result.

solution / workaround

@sunny's reply technically works, big tables millions of records, it's performance awful. insted, did workaround creating table , inserting records need there. here sql

insert log_search_simple select id, searchstring, max(logdate) logdate, locale log_search locale = 'en' , results > 0 grouping searchstring order logdate desc

your seek right need select 1 column of id instead of multiple columns.

try that.

 delete log_search log_search id not in (select id log_search locale = 'en' , results > 0 grouping searchstring )

mysql sql