Delete with joined table

Question

I have two table structures here:

employee 
(emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(100),
emp_ic VARCHAR(20),
emp_phone VARCHAR(20),
emp_address VARCHAR(200))

employee_medical
(id INT AUTO_INCREMENT,
emp_id INT,
medical_notes TEXT,
PRIMARY KEY (id),
FOREIGN KEY (emp_id) REFERENCES employee(emp_id))

I want to delete row from two table which is emp_id but When I tried the following it didn't work.

$query = "DELETE employee, employee_medical
FROM employee
LEFT JOIN employee ON employee.emp_id = employee_medical.emp_id
WHERE emp_id='$id'";

Error : Not unique table/alias: 'employee'

How can I fix this?

Try adding employee.emp_id as shown in the query : $query = "DELETE employee, employee_medical FROM employee LEFT JOIN employee ON employee.emp_id = employee_medical.emp_id WHERE employee.emp_id='$id'"; — Kamal Joshi, Aug 17 '15 at 14:34

score 2 · Answer 1 · edited May 23 '17 at 12:22

Your query is wrong. It should not be DELETE employee FROM employee, but simply DELETE FROM employee.

Your JOIN is also incorrect as you seem to be trying to join the same 2 tables together (you join employee with employee).

Finally in your WHERE clause you should specify from which table it should check the emp_id. Also you do not need the single quotes around your $id in this case. It's a number, not a string.

$query = "DELETE FROM employee
LEFT JOIN employee_medical ON employee.emp_id = employee_medical.emp_id
WHERE employee.emp_id=$id";

Do take a look at this question though, I believe it's in your interest. A DELETE CASCADE is probably what you need instead of a JOIN.

score 0 · Answer 2 · answered Aug 17 '15 at 15:16

0

My preference is to be explicit about the tables you are deleting from (as well as using table aliases).

DELETE e, em
    FROM employee e LEFT JOIN
         employee_medical em
         ON e.emp_id = em.emp_id
WHERE e.emp_id = $id;

answered Aug 17 '15 at 15:16

Gordon Linoff

1,242,037
58
646
786

Delete with joined table

2 Answers2