Error on MySQL multi-table DELETE

Question

Anyone could tell me what is wrong with the following mysql query?

DELETE FROM table1 as A, table2 as B WHERE A.delivery_status = '3' AND
A.delivered_on < '2015-11-26' AND A.delivery_id = B.delivery_id

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 as A, table2 as B WHERE A.delivery_status = '3' AND A.delivered_on 'at line

I also tried < DATE('2015-11-26') because delivered_on is a datetime field, with no success.

Possible duplicate of How to delete from multiple tables in MySQL? — Hecke29
– Hecke29, Commented Nov 26, 2015 at 9:04

nagiyevel · Accepted Answer · 2015-11-26 08:51:35Z

3

Table aliases in a multiple-table DELETE should be declared only in the table_references part of the statement. Elsewhere, alias references are permitted but not alias declarations. You must type:

DELETE A,B FROM table1 as A, table2 as B WHERE A.delivery_status = '3' AND
A.delivered_on < '2015-11-26' AND A.delivery_id = B.delivery_id

answered Nov 26, 2015 at 8:51

nagiyevel

4345 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Parth Chavda · Accepted Answer · 2015-11-26 08:45:14Z

0

try this

DELETE FROM table1  A, table2  B WHERE A.delivery_status = '3' AND
A.delivered_on < '2015-11-26' AND A.delivery_id = B.delivery_id

answered Nov 26, 2015 at 8:45

Parth Chavda

1,8291 gold badge24 silver badges31 bronze badges

Comments

user1847437 · Accepted Answer · 2015-11-26 12:09:42Z

0

This works fine for me:

DELETE table1 FROM table1 LEFT JOIN table2 ON table1.delivery_id = table2.delivery_id WHEREtable1.delivery_status = '3' AND table2.delivered_on < DATE($today) AND table1.delivery_id = table2.delivery_id";

edited Nov 26, 2015 at 12:09

answered Nov 26, 2015 at 9:23

user1847437

2131 gold badge3 silver badges12 bronze badges

Collectives™ on Stack Overflow

Error on MySQL multi-table DELETE

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related