1

A database I am in charge of fixing got filled with spam. There is a table called url in the database. There is a column called alias. There is 2,000 correct alias rows that should be there and the other 100,000+ are spam. I have a comma separated list of all the correct values that should be there. An example value in Alias would be z6j6h and they are all 5 letters/numbers. How can I delete every row that does not contain a value in the good list?

JediTricks007
  • 258
  • 3
  • 10

4 Answers4

2

Use the find_in_set() function:

delete from mytable
where find_in_set(alias, 'abcde,ghijk,etc') = 0
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Hi this seems backwards. This would delete abcde,ghijk,etc. I need it to delete everything but abcde,ghijk,etc. – JediTricks007 Jun 14 '15 at 01:07
  • @Bohemian - the comma-separated list has all the good aliases, which shouldn't be deleted. The condition should be `find_in_set(alias, 'blah,foo,etc') = 0`. – Ed Gibbs Jun 14 '15 at 01:07
  • @edg missed that, thanks - I've adopted your suggestion into my answer – Bohemian Jun 14 '15 at 03:43
2
DELETE
  FROM (table)
 WHERE alias NOT IN ('ASDFG', 'ABCDE')
Pang
  • 9,564
  • 146
  • 81
  • 122
bashaus
  • 1,614
  • 1
  • 17
  • 33
1

Not at a console-apologies for terseness! One way would be to put your correct values in another table and run an outer join on the main table and the correct-values table. That will give you all the rows of the main table, with NULLs flagging the spam. Then you delete rows having those nulls. See https://stackoverflow.com/a/9214674/2877364 for a join example and https://stackoverflow.com/a/2763245/2877364 for deleting.

Community
  • 1
  • 1
cxw
  • 16,685
  • 2
  • 45
  • 81
0
delete from url
where alias not in (
  'my',
  'list',
  'of',
  'good',
  'aliases'
);
John
  • 3,458
  • 4
  • 33
  • 54