CODEWITHSUNDEEP
mysqlsqldatabase
xms
xms

Reputation: 437

MySQL and deleting several rows with INNER JOIN

I'm using MySQL. I would like to delete all matches which chart_id = 12 and places.match_no > 104.

Anyway, my query is not working. I cannot see what is the reason.

DELETE FROM matches
  INNER JOIN places ON places.id = matches.place_id
    AND places.match_no > 104
WHERE matches.chart_id = 12

This will cause an error:

SQLSTATE[42000]: Syntax error or access violation: 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 'INNER JOIN places ON places' at line 2

Upvotes: 0

Views: 47

Answers (3)

alaa_sayegh
alaa_sayegh

Reputation: 2211

try this one:

    DELETE m
    FROM matches m INNER JOIN
    places p
    ON p.id = m.place_id
    WHERE m.chart_id = 12 AND p.match_no > 104

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You need to specify the table you want to delete from:

DELETE m
    FROM matches m INNER JOIN
         places p
         ON p.id = m.place_id AND p.match_no > 104
    WHERE m.chart_id = 12;

The table name/alias goes between the DELETE and the FROM.

This is clearly explained in the documentation:

Multiple-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Upvotes: 2

Radim Bača
Radim Bača

Reputation: 10701

Another option is:

DELETE FROM matches
WHERE matches.chart_id = 12 and matches.place_id in 
(select place_id 
 from places 
 where match_no > 104
)

Upvotes: 1

Related Questions