CODEWITHSUNDEEP
phpmysql
David
David

Reputation: 437

Finding and deleting one record from two tables ? MySQL PHP

I have two MySQL tables, one with the field Username and the other User_Name, is it possible to find a record in one of the tables and delete it? I've tried the following but it didnt work...

DELETE FROM JB_Employer, JB_Jobseeker WHERE Username='$userName' OR User_Name='$userName'

I feel as though I'm missing something obvious..

Thanks. Kai

Upvotes: 0

Views: 219

Answers (1)

Abhay
Abhay

Reputation: 6645

There is a difference in syntaxes when FROM is used before or after the table names from which rows are to be deleted. These should work for deleting entries from both tables:

Using FROM after the table-names:

DELETE JB_Employer, JB_Jobseeker 
FROM JB_Employer INNER JOIN JB_Jobseeker ON JB_Employer.Username = JB_Jobseeker.User_Name
WHERE JB_Employer.Username= '$userName';

Using FROM before the table-names:

DELETE FROM JB_Employer, JB_Jobseeker 
USING JB_Employer INNER JOIN JB_Jobseeker ON JB_Employer.Username = JB_Jobseeker.User_Name
WHERE JB_Employer.Username= '$userName';

EDIT 1

The below section explains the queries for deleting the entries from both tables or either table.

Using FROM after the table-names:

DELETE JB_Employer, JB_Jobseeker 
FROM JB_Employer
LEFT JOIN JB_Jobseeker ON JB_Employer.Username = JB_Jobseeker.User_Name
WHERE JB_Employer.Username= '$userName';

Using FROM before the table-names:

DELETE FROM JB_Employer, JB_Jobseeker 
USING JB_Employer
LEFT JOIN JB_Jobseeker ON JB_Employer.Username = JB_Jobseeker.User_Name
WHERE JB_Employer.Username= '$userName';

Hope it works!

Upvotes: 1

Related Questions