Error in Delete SQL syntax

Question

ok guys i need your help im having trouble in mysql syntax

i have a table of

CREATE TABLE tblemployee(
`employeeID` int(5) Primary key not null,
`employeefname` varchar(15),
`employeelname` varchar(15),
);

CREATE TABLE tbltimpunch(
`employeeID` varchar(10),
`dateoftime` datetime,
`timein` time,
`timeout` time
);

and i want to delete this:

DELETE FROM tblemployee t,tblemployee e 
WHERE t.employeeID = e.employeeID 
and e.employeelname ='EnterNumber'
and dateoftime ='2013-07-02' 
and timein ='09:00:00' 
and timeout = '15:00:00'

this is my error:

Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE t.employeeID = e.employeeID and e.employeelname ='EnterNumber' and dateoft' at line 1

Answer 1

First thing:

DELETE FROM tblemployee t,tblemployee e

These are both same tables, so thats why you're getting error. Guess it should be:

DELETE FROM tbltimpunch t,tblemployee e

Because you're deleting from multiple tables, query should be something like this:

DELETE t, e 
FROM tbltimpunch t 
INNER JOIN tblemployee e  
WHERE CAST(t.employeeID AS SIGNED) = e.employeeID  
AND e.employeelname ='EnterNumber' 
AND dateoftime ='2013-07-02'  
AND timein ='09:00:00'  
AND timeout = '15:00:00'