0

I can't figure out why my request doesn't work. I want to delete the hotspots in the table hotspot and the laptop that uses these hotspots in the table laptop. given a user ID = 2EA81 here it is :

DELETE FROM hotspot, laptop WHERE hotspot.user_id= '2EA81' and laptop.id= hotspot.hotspot_ID

Error Code: 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 'WHERE hotspot.user_id= '2EA81' and laptop.id= ' at line 1

Any idea ?

1
  • What is the full error? The multi-table delete syntax requires you specify after the DELETE keyword which table you want to delete from (like DELETE hotspot FROM hotspot JOIN laptop...), and also may not support an implicit inner join. Commented Jun 23, 2015 at 15:59

1 Answer 1

1

MySQL's multi-table DELETE syntax expects that you list the tables where rows should actually be deleted to be listed just after the DELETE keyword - it will not automatically delete from all joined tables.

If you wish to delete rows from both hotspots and laptop, you'll need to list them after DELETE. I would also recommend changing your join expression to an explicit INNER JOIN, as described in the table_references section of the linked documentation. MySQL should support an implicit (comma-separated FROM as you attempted) in a multi-table delete - I have not tested, but the explicit INNER JOIN is preferred anyway as a modern syntax.

DELETE
  -- list tables to actually delete rows from
  hotspot,
  laptop
FROM
  -- Supply an explicit INNER JOIN
  hotspot
  INNER JOIN laptop ON laptop.id= hotspot.hotspot_ID
WHERE 
  -- Filter rows on conditions
  hotspot.user_id= '2EA81'

Listing the tables to delete differs from the single table DELETE syntax, where you needn't name any tables between DELETE and FROM because the single table in the FROM clause is implicit as your deletion target.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.