(er_parse_error) #1064

Question

I have tried this 3 different ways for my delete. I would like to delete the row from table buylist_products if the conditions are ok.

try 1 :

DELETE FROM parkage_buylist_products bp, parkage_productsmeta pm, parkage_buylist b WHERE bp.product_id = pm.product_id AND (pm.meta_key = 'we_buy_price' OR pm.meta_key = 'we_buy_rule') AND pm.meta_value = 0 AND bp.order_id = b.order_id AND b.status = 'in_progress' AND b.order_id = 24

try 2 :

DELETE FROM parkage_buylist_products bp  
LEFT JOIN parkage_productsmeta pm, parkage_buylist b  
ON bp.product_id = pm.product_id 
WHERE (pm.meta_key = 'we_buy_price' OR pm.meta_key = 'we_buy_rule') 
AND pm.meta_value = 0 
AND bp.order_id = b.order_id 
AND b.status = 'in_progress' 
AND b.order_id = 24

try 3 :

DELETE FROM parkage_buylist_products bp, parkage_buylist b    
WHERE bp.order_id = b.order_id 
AND b.status = 'in_progress' 
AND b.order_id = 24 
AND bp.product_id IN (SELECT pm.product_id 
                  FROM parkage_productsmeta pm
                WHERE (pm.meta_key = 'we_buy_price' OR pm.meta_key = 'we_buy_rule') 
                AND pm.meta_value = 0

For the first one i have tried, delete bp, delete bp.* which it gives me an error on syntax.

Thank you for your help

1) What about the other 2 attempts? 2) Pls share the exact error message with us. — Shadow, Apr 13 '17 at 08:59
for the 3 attempts i have the same error : #1064. I am in phpMyAdmin it doesn not show more details — Pet, Apr 13 '17 at 09:02
Yes, it does. phpmyadmin displays the full MySQL error message. — Shadow, Apr 13 '17 at 09:21
I'm not familiar with phpMyAdmin - do you need to change setting to get it to display / stop displaying message? — toonice, Apr 13 '17 at 09:22
Possible duplicate of [How can I fix MySQL error #1064?](http://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064) — Daniel W., Apr 13 '17 at 09:22

score 0 · Accepted Answer · edited May 23 '17 at 12:17

0

Please try the following...

DELETE parkage_buylist_products,
       parkage_productsmeta,
       parkage_buylist
FROM parkage_buylist_products
JOIN parkage_productsmeta
JOIN parkage_buylist
WHERE parkage_buylist_products.product_id = parkage_productsmeta.product_id
  AND parkage_buylist_products.order_id = parkage_buylist.order_id
  AND ( parkage_productsmeta.meta_key = 'we_buy_price' OR
        parkage_productsmeta.meta_key = 'we_buy_rule' )
  AND parkage_productsmeta.meta_value = 0
  AND parkage_buylist.status = 'in_progress'
  AND parkage_buylist.order_id = 24;

After doing a quick search using the criteria delete from many tables at once sql I found the page at delete from two tables in one query. I took the second option from their most popular Answer (by Eric) and modified it with your code.

Based on my research, this should delete from the tables specified after DELETE those records that meet the criteria specified, which are the same as in your Question. Please note that I have stripped the aliases away - I prefer working without them where I can. Presumably you can still use them here.

If you have any questions or comments, then please feel free to post a Comment accordingly.

edited May 23 '17 at 12:17

Community

1
1

answered Apr 13 '17 at 09:05

toonice

2,211
1
13
20

It does not like the elements after DELETE. I dont understand why. it says : Unexpected token. A question, would it delete the rows corresponding from the 3 tables? – Pet Apr 13 '17 at 09:10
Second version added. Please try that. (Sorry, but this is my first time trying to delete across multiple tables in one statement). – toonice Apr 13 '17 at 09:12
Oopsis, your thing is working very well thank you! I erased a letter on the pasting thing :/ Thank you very much – Pet Apr 13 '17 at 09:18
That is good to hear! I have updated my Answer with an explanation. Feel free to tick this Answer if it has suitably addressed your needs, and / or to ask any follow up questions you might have. – toonice Apr 13 '17 at 09:21

(er_parse_error) #1064

1 Answers1