March 9, 2006 at 1:21 pm
Hi,
I need to delete records from two tables, but they are not linked by foreign keys, or anything like that. However there is a possible join.
Table A consists of:
StoreID Int
OrderID Int
OrderDate datetime
...
Table B consists of:
StoreID int
OrderID Int
ProductID int
Qty int
...
...
So I need to delete all records from TableA that occur before a given date, and to delete the associated records in TableB to free up space.
I came up with the following query:
DELETE FROM TableB b
LEFT JOIN (
Select StoreID, OrderID, OrderDate
FROM TableA
WHERE TableA.OrderDate=CONVERT(DATETIME, '2005-08-01', 102)
) A
ON
a.StoreID=b.StoreID AND
a.OrderID=b.OrderID
Unfortunalely, SQL doesnt like it. Am I making a simple mistake. surely this should work?
Any response gratefully appreciated.
Andy
March 9, 2006 at 1:29 pm
You would need to use two statements I would think, and the tableb one you use above as an example, would surely be an inner join, not a left join right?
If table A has the criteria, and you want to delete from table B by that criteria, an inner join would give this. Left join, if it did work, would delete all records in table b.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply