INNER JOIN works as SELECT but not as DELETE

Question

Why does this have a syntax error

DELETE FROM `print_mailing_request` pmr
INNER JOIN `person` p
  ON p.id = pmr.person AND p.email LIKE '%@TEST.COM'
WHERE 
pmr.sent_to_fulfiller != 'y'

when this works correctly without error?

SELECT * FROM `print_mailing_request` pmr
INNER JOIN `person` p
  ON p.id = pmr.person AND p.email LIKE '%@TEST.COM'
WHERE 
pmr.sent_to_fulfiller != 'y'

Since one is SQL Server 2008 and mine is mySQL (tagged but not explicitly stated) I wasn't sure that post was relevent to mine and moreover I tried to implement that solution... — jerrygarciuh, Jun 18 '14 at 22:11

score 7 · Answer 1 · answered Jun 18 '14 at 21:59

You need to tell delete what table to delete from:

DELETE pmr
    FROM `print_mailing_request` pmr INNER JOIN
         `person` p
         ON p.id = pmr.person AND p.email LIKE '%TEST.COM'
    WHERE pmr.sent_to_fulfiller != 'y';

The table alias goes after the delete statement and before the from. I am assuming it is the first table mentioned.

score 3 · Accepted Answer · answered Jun 18 '14 at 22:06

Correct syntax would look like below without using table alias. You are missing the table name to delete from.

DELETE `print_mailing_request`
FROM `print_mailing_request`
INNER JOIN `person` 
  ON `person`.id = `print_mailing_request`.person 
  AND `person`.email LIKE '%@TEST.COM'
WHERE `print_mailing_request`.sent_to_fulfiller != 'y'

INNER JOIN works as SELECT but not as DELETE

2 Answers2

Linked