0

i have two table like this: tbl1-

ID | post_id | terms_id
------------------------
1     4001      2
2     4001      1
3     4001      3
4     4002      5
5     4002      2

tbl2-

terms_id | taxonomy_id | taxonomy
----------------------------------
1          30             category
2          33             keywords
3          42             location
4          52             category
5          32             category

I want two delete tbl1 where post_id = 4001 and (terms_id = 'category') from tbl2. i can't write this query how to set relation tbl1 terms_id and tbl2 terms_id and tbl2 terms_id should be 'category'. i tried to use inner join but it delete both table data or it shows error. i just need to delete from tbl1 data.
This is my query:

DELETE FROM tbl1 
INNER JOIN tbl2 ON tbl2.terms_id = tbl1.terms_id 
WHERE tbl2.taxonomy = 'category' AND tbl1.post_id = 4001
forpas
  • 160,666
  • 10
  • 38
  • 76
sajib
  • 11
  • 3
  • 1
    Possible duplicate of [Delete with Join in MySQL](https://stackoverflow.com/questions/652770/delete-with-join-in-mysql) – Dharman Jun 09 '19 at 10:53
  • 1
    Show us the query you have tried then. If your query with a join deleted too much there must be an error in it. – Dharman Jun 09 '19 at 10:57
  • DELETE FROM tbl1 INNER JOIN tbl2 ON tbl2.terms_id = tbl1.terms_id WHERE tbl2.taxonomy = 'category' AND tbl1.post_id = 4001 – sajib Jun 09 '19 at 11:04
  • 1
    You are close, just change to `DELETE tbl1 FROM tbl1 INNER JOIN ....` – forpas Jun 09 '19 at 11:07

1 Answers1

1

The tables involved in delete are declared in DELETE clause so you should declare juts the table name you need to delete the rows

This should delete only from tbl1 based on matching result from the join tables 

DELETE tbl1
FROM tbl1
INNER JOIN tbl2 ON tbl1.post_id  = 4001 
  and tbl1.terms_id  = tbl2.terms_id 
    and  tbl2.taxonomy = 'category'
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • This is very similar to the duplicate. – Dharman Jun 09 '19 at 11:14
  • thank you brother i got it...and its work. thank you so much – sajib Jun 09 '19 at 11:14
  • @scaisEdge Out of curiosity, why did you place the filtering predicate in the `JOIN` clause rather than a `WHERE` clause? This is an inner join so it shouldn't make any difference. – The Impaler Jun 09 '19 at 15:18
  • @TheImpaler As I usually do, I tend to include clauses as explicit elements of joins, it is clearer to me and I can keep the related elements better organized. But in this case the predicates seem to me an integral part of the join – ScaisEdge Jun 09 '19 at 16:59