1

I want to delete a row from a table of MySQL database

SQL Query:

DELETE FROM students WHERE tutor_availability = student_availability;

tutor_availabilty is contained within another table called tutors. Possibly worth noting that I am using xampp. Wondering if anyone could help me with this?

nalply
  • 26,770
  • 15
  • 78
  • 101
bano590
  • 39
  • 1
  • 8

2 Answers2

2

Depending on what it is you are actually trying to delete and how your records are related you may want to use and IN instead of a JOIN. This may also be a little easier to visualize.

For example:

DELETE FROM students where student_availability 
IN (Select tutor_availability FROM tutors)

Here is a good explanation of JOIN vs IN:

SQL JOIN vs IN performance?

Community
  • 1
  • 1
AhDev
  • 486
  • 11
  • 16
  • I didn't think about the fact that this is in MySQL. You can use double nesting with a DELETE in MySQL apparently. Here is an example : http://stackoverflow.com/questions/7298504/mysql-delete-with-nested-select-query – AhDev Feb 26 '14 at 14:53
  • No I am sorry, I htink this will work as you wrote as it is not the same table. I just confounded the 2 queries... my mistake – CodeBird Feb 26 '14 at 15:00
  • that is the right answer, thank you so much raholling – bano590 Feb 26 '14 at 19:58
0

Didn't get the full picture here, but after reading like 10 times i think you have the following structure

  • Table - Stutends (id, student_avaliability, tutor_id...)
  • Tutors (id, tutor_avaliability, ... )

So, you may want to try this:

"DELETE FROM students WHERE students.student_availability = tutors.tutor_availability INNER JOIN tutors ON (students.tutor_id = tutors.id)"

Hugo Dias
  • 169
  • 3
  • 14