-1
DELETE *
FROM ((disease
INNER JOIN dishead ON disease.heading = dishead.hid)
INNER JOIN disdes ON disease.description = disdes.did)
where disease.id = 9;

basically i have inserted the data using inner join and the data is stored against same product in multiple tables Now i am trying to write query for deleting that inserted rows from all tables

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Abdullah Khan
  • 1
  • 1
  • There is no asterisk in DELETE, only SELECT. – Funk Forty Niner Jul 02 '20 at 17:59
  • When you delete with an inner join, you have to specify the table(s) to delete from after `DELETE`. – Barmar Jul 02 '20 at 18:01
  • DELETE operates on records, not columns. So the `*` for DELETE isn't meaningful. Also, I assume you want to delete from an actual table or tables, so those must be specified. – lurker Jul 02 '20 at 18:01
  • @FunkFortyNiner That's not the right dup for this. There's no `!=` here, and that question doesn't have `JOIN`, which changes the syntax. – Barmar Jul 02 '20 at 18:04
  • @Barmar It's one of them. If you feel it should be reopened, let me know of you can do it. Or... to find another duplicate to add on to this. – Funk Forty Niner Jul 02 '20 at 18:05
  • *funkyfortyniner True, but just to note MySQL allows `DELETE x.* FROM x` – Strawberry Jul 02 '20 at 18:06
  • @FunkFortyNiner Which one of them? I can't even find the word `join` anywhere in the question or answers. – Barmar Jul 02 '20 at 18:06
  • @Barmar Oh for crying out loud. No "join". So which "join" tag should I have used? I should just roll back and reopen. – Funk Forty Niner Jul 02 '20 at 18:09

1 Answers1

0

You have to specify the tables to delete from

DELETE d, dh, dd
FROM disease AS d
JOIN dishead AS dh ON d.heading = dh.hid
JOIN disdes AS dd ON d.description = dd.did
WHERE d.id = 9

Note also that if these are foreign keys, you can configure them with ON DELETE CASCADE. Then you only have to delete from the parent table, and the rows that reference them will be deleted automatically.

Barmar
  • 741,623
  • 53
  • 500
  • 612