MySql Delete not working

Question

I have a mysql delete script which gives me an error:

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 'LEFT JOIN mall ON mall.m_id = unifo.mids WHERE mallnames = 'My Mall'' at line 2

Delete query:

DELETE FROM unifo 
LEFT JOIN mall ON mall.m_id = unifo.mids 
WHERE mallnames = 'My Mall' && time_insert < NOW( ) - INTERVAL 25 
MINUTE

I cannot find my mistake

score 2 · Accepted Answer · answered Jul 03 '16 at 03:23

you have a basic issue in this query

DELETE FROM unifo 
LEFT JOIN mall ON mall.m_id = unifo.mids 
WHERE mallnames = 'My Mall' && time_insert < NOW( ) - INTERVAL 25 
MINUTE

your performing a left join with mall table and then base on mall value your going to delete something but in left join mallname can be null no point of left joining it

2 specify the table

DELETE unifo FROM unifo ....

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

1

You may need to say which table you are deleting from, as in this answer: Delete with Join in MySQL

edited May 23 '17 at 12:32

Community

1
1

answered Jul 03 '16 at 03:23

dcc310

1,038
1
9
13

score 0 · Answer 3 · answered Jul 03 '16 at 03:33

You have two tables in the from, but do not specify which to delete from.

I would recommend uses table aliases as well:

DELETE u
    FROM unifo u LEFT JOIN
         mall m
         ON m.m_id = u.mids 
WHERE mallnames = 'My Mall' AND time_insert < NOW( ) - INTERVAL 25 MINUTE ;

This assumes that you want to delete the rows from unifo.

MySql Delete not working

3 Answers3