1

Researching similar common queries have really helped but I am still none the wiser as to my issue. To note that the database I intend to use this on contains around 1000 rows (same 2 columns listed below) of which 300-350 will need deleting depending on their ID.

E.g. I think the query should be something like (table = mytable):

Title ID
One   1
Two   2
Three 3
Four  4

delete from mytable
where ID = 1 or 2 or 3;

Obviously when it comes to deleting 300 odd rows, putting an operator inbetween every variable probably isn't the best way of this (nor probably correct anyway) hence why I am asking this question!

Any advice would be much appreciated, thanks!

Sam Ryder
  • 23
  • 2

3 Answers3

1

You could use the IN operator for example

WHERE id IN (1,2,3)

How are you deciding which rows you want deleting, that might help us answer?

Phil Stephenson
  • 121
  • 3
  • Well the table is used to hold contact details within a CMS where users have created duplicate titles with different ID's. I'm basically cleansing the table and getting rid of any contact rows that are duplicated. So if I want the customers to only use ID 1 but they have created ID 2 and ID 3, all with the same name, I want to delete ID 2 and ID 3. – Sam Ryder Jan 16 '14 at 11:21
1

If I understand you

DELETE FROM mytable WHERE id NOT IN(SELECT id FROM(
  SELECT MIN(id)as id FROM mytable 
    GROUP BY title 
    HAVING COUNT(title)>1 
UNION
  SELECT id FROM mytable 
    GROUP BY title 
    HAVING COUNT(title)=1)x);

SQL Fiddle

Or simpler per ypercube sugesstion

SQL Fiddle

Mihai
  • 1,543
  • 1
  • 13
  • 16
  • 1
    Why not just ... WHERE id NOT IN (SELECT * FROM (SELECT MIN(id) FROM mytable GROUP BY title) x) – ypercubeᵀᴹ Jan 16 '14 at 12:00
  • Hi Mihai, that seems to have the outcome I was looking for and definitely a quicker way than listing every ID I wanted to delete, thank you. I'll be honest though I was trying to avoid using Title (even though it is clearly more effective) as some customers have listed variables with "and" rather than "&" but I guess that would just mean I run the query twice for essentially the same name. Either way, I appreciate your help with finding a solution for me, thanks! – Sam Ryder Jan 16 '14 at 12:05
  • Try to use REPLACE on that column or use SUBSTRING_INDEX if there is a discerning pattern. – Mihai Jan 16 '14 at 12:06
  • Just so I am following your solution correcty, it is the lowest ID value that will be ignored whilst anything above that value will be deleted? So, ID 5 will be deleted over ID 1 however if I wanted to keep ID 533 but delete ID 532, this will not work? Which is fine by the way, just means I will swap some contacts I have chosen to keep to the one with the lowest ID in that group. – Sam Ryder Jan 16 '14 at 12:12
  • Yes,for each group(that is, ids with common title) the minimum id will be selected.One thing you could do is swap it with MAX and you`ll have the largest id per group.http://sqlfiddle.com/#!2/81dbf/1 Or,use MAX(id)-some number,the issue is that no matter what option you select it will be applied to all. – Mihai Jan 16 '14 at 13:14
1

With the help of editor obviously we can use loops as you, but in this case, if you are know particular id's for deleting then you can use this.

DELETE FROM mytable WHERE id in(1,2,3,...);

SagarPPanchal
  • 183
  • 1
  • 7