what's wrong with this delete statement

Question

I have this sql statement to delete some rows:

DELETE 
FROM
  `user_group_membership` g
  INNER JOIN `users` u ON u.user_id = g.user_id
WHERE
  u.`user_id` NOT IN (?, ?, ?)
  AND g.group_id IN (9, 6)
  AND u.`lifetime_supporter` = 0
  AND u.supporter_type = 'special'

I get an SQL error of "SQL 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 'g INNER JOIN users u ON u.user_id = g.user_id WHERE u.user_id NOT IN ( ?,?,?"

Not entirely sure what's wrong here, could someone give me a pointer?

https://stackoverflow.com/questions/2763206/deleting-rows-with-mysql-left-join?rq=1 — user154983, Nov 08 '18 at 10:40

score 1 · Accepted Answer · answered Nov 08 '18 at 10:46

You need to specify from which table you need to Delete the record. If you want to delete from Table user_group_membership, you need to specify table after DELETE Command like below:


DELETE g
FROM
  `user_group_membership` g
  INNER JOIN `users` u ON u.user_id = g.user_id
WHERE
  u.`user_id` NOT IN (?, ?, ?)
  AND g.group_id IN (9, 6)
  AND u.`lifetime_supporter` = 0
  AND u.supporter_type = 'special'

what's wrong with this delete statement

1 Answers1