0

trying to delete data from two tables and getting error Incorrect syntax near ','.

DELETE tblItem, tblCategory
FROM tblCategory
INNER JOIN tblItem
WHERE tblCategory.category_id = tblItem.fk_category

Update

enter image description here

What's wrong?

MWN
  • 377
  • 1
  • 5
  • 17

2 Answers2

2

DELETE can only delete from one table at a time, so you will have to do two separate delete statements, in the correct order:

DELETE
FROM tblItem
WHERE fk_category IN (...)

and then

DELETE
FROM tblCategory
WHERE category_id = (...)

First we can try deleting all items belonging to certain target categories. Then, once those child records have been deleted, we can delete the parent category records.

A better way to handle this is to use something like ON DELETE CASCADE, which means that if a parent category in tblCategory were to be deleted, SQL Server would automatically propagate that delete to all child item records in tblItem.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • deleting data from two tables, not a single – MWN Jun 25 '18 at 06:19
  • using `tblCategory.*, tblItem.*`, now giving me error of `Incorrect syntax near '*'.` – MWN Jun 25 '18 at 06:29
  • Well that's not the syntax I suggested in my answer. Did you try my answer? – Tim Biegeleisen Jun 25 '18 at 06:30
  • I copied and pasted your syntax, still getting `Incorrect syntax near '*'.` – MWN Jun 25 '18 at 06:33
  • @MWN I think both these answers were misleading you, because I don't think it's possible to delete from two tables at once in SQL Server. Nor should you really have a good reason to want to do this. – Tim Biegeleisen Jun 25 '18 at 06:39
  • Yes, I deleted the associating rows with two operations but I checked on `stackover-flow` that some guys `up-voted` the syntax that you suggest earlier for deleting data from two tables but unfortunately this did not work for me. – MWN Jun 25 '18 at 06:40
  • I think you were looking at a MySQL question. Were you looking [here](https://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server)? Some of those answers look like MySQL syntax to me. – Tim Biegeleisen Jun 25 '18 at 06:41
  • Thanks for your time and effort – MWN Jun 25 '18 at 06:49
1

try like this

   DELETE from 
      FROM tblCategory where category_id  in (select  fk_category from  tblItem  )


   DELETE from 
      FROM tblItem  where fk_category in (select  category_id  from  tblCategory )
Chanukya
  • 5,833
  • 1
  • 22
  • 36