Can't delete duplicate rows in a table

[stevepatd]

I am trying to delete some rows that are duplicate, or almost duplicates, from a table.   I've spent too many hours trying different approaches and researching this and have to ask for help. 

From the test "print" statement I put in, it never gets inside the if statement, I never see "I am here" on the screen.  Not that it matters but I'm using php. 

From the attached picture of my database table, I want to delete the lines, with red arrows, that have duplicate dates/times that have the word 'hide' for the Code.  If both similar lines have 'hide' for the code then I only want one, remove the other (thus the Limit 1).   Any help is appreciated.  Thank you.

$remove = "DELETE FROM teacher_schedule
           WHERE (Code='hide')
           ORDER BY DateTime
           LIMIT 1";
if (@mysqli_query ($connection,$remove)) {
  print "I am here";
  }

 

Edited April 15, 2022 by stevepatd
add clarification

[Barand]

What is your table's definition? Does each record have a unique id?

[Barand]

As all records in a relational DB should have a primary key I'll assume yours do.

BEFORE

+---------------------+------+----------------+
|                   1 | hide | 20211123073000 |
|                   2 | hide | 20211123073000 |
|                   3 | hide | 20211123074500 |
|                   4 | hide | 20211123074500 |
|                   5 |      | 20211123080000 |
|                   6 | hide | 20211123080000 |
|                   7 |      | 20211123081500 |
|                   8 | hide | 20211123081500 |
|                   9 |      | 20211123083000 |
|                  10 | hide | 20211123083000 |
+---------------------+------+----------------+

QUERY

DELETE teacher_schedule
FROM teacher_schedule
     JOIN
     (
		SELECT code
			 , sched_time
			 , MAX(teacher_schedule_id) as teacher_schedule_id
		FROM teacher_schedule
		WHERE code = 'hide'
		GROUP BY code, sched_time
		HAVING count(*) > 1
	  ) dupes USING (teacher_schedule_id);

AFTER

+---------------------+------+----------------+
| teacher_schedule_id | code | sched_time     |
+---------------------+------+----------------+
|                   1 | hide | 20211123073000 |
|                   3 | hide | 20211123074500 |
|                   5 |      | 20211123080000 |
|                   6 | hide | 20211123080000 |
|                   7 |      | 20211123081500 |
|                   8 | hide | 20211123081500 |
|                   9 |      | 20211123083000 |
|                  10 | hide | 20211123083000 |
+---------------------+------+----------------+

 

[stevepatd]

I allow duplicates since the administrator can double book, I want to do the cleanup in the backend.  I gave some thought to having an integer  index like you show but thought I could get away without it.  I will add that.  The table will eventually have data from several schools so I do have a column for SchoolID.  Will your approach allow me to keep the data from other school buildings intact?  Thanks for the help, I will give this a try.

 

[Barand]

24 minutes ago, stevepatd said:

the administrator can double book

Is that because they want or need to, or because there is nothing to stop them them doing it accidentally?

You could add a UNIQUE constrint on (code, datetime) columns to prevent accidental duplicates.

BTW, as you will be testing deletions (not undoable) I recommend you back up before testing on any live data.

Edited April 15, 2022 by Barand

[stevepatd]

This is for setting up parent teacher conferences.  Sometimes they will set up one 'day' as the morning, then the second 'day' as the afternoon of the same day as that morning session.  I want to merge the two getting rid of the hidden timeslots and keeping the ones that are open to schedule.   I wish they wouldn't do this but instead of giving them an error message I would rather handle it.

Thanks for the help, it seems to be working now.

Edited April 15, 2022 by stevepatd
spelling mistake