I tried to delete a joining tables with the same lbhc_lb_sku in table lif_bookhascategory and lb_sku in table lif_books. I can't delete it.
Here's my SQL command:
DeleteCommand="DELETE FROM lif_books FROM lif_books CROSS JOIN lif_bookshascategory WHERE (lif_books.lb_sku = @lb_sku) AND (lif_bookhascategory.lbhc_lb_sku = @lb_sku)">
That returns:
Incorrect syntax near the keyword 'INNER'.
Officially, the SQL specification does not provide for using a Join in an action query (Insert, Update or Delete). In this case, it is simpler to use a more universal format:
Delete lif_books
Where lb_sku = @lb_sku
And Exists (
Select 1
From lif_bookhascategory
Where lbhc_lb_sku = @lb_sku
)
I just tested this on my own database and it works.
DELETE lif_bookshascategory, lif_books
FROM lif_bookshascategory
INNER JOIN lif_books
WHERE lbhc_lb_sku = lb_sku
This should work:
DELETE lif_bookshascategory, lif_books
FROM lif_bookshascategory
INNER JOIN lif_books
ON lif_bookshascategory.lbhc_lb_sku = lif_books.lb_sku
