CODEWITHSUNDEEP
phpmysqljoinsql-delete
Oscar Ho
Oscar Ho

Reputation: 15

Delete with joined table

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?

Upvotes: 0

Views: 59

Answers (2)

Praxis Ashelin
Praxis Ashelin

Reputation: 5217

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.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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;

Upvotes: 0

Related Questions