February 16, 2011 at 10:25 am
I'm not a wiz at sql. I have 2 tables that have a relationship to each other. I want to delete the rows from table 2 that have rows associated to them in table 1.
I wrote a select statement to give me all rows in table 2 where the ID fields match the ID fields in table 1. That part is fine, but I now want to delete those results from table 2.
Can I modify this simple select statement to then delete the results? My select statement is:
SELECT TOP 100 PERCENT dbo.Table_2.[UNIQUE #], dbo.Table_2.[SEQ ORDER #]
FROM Table_1 INNER JOIN
dbo.Table_2 ON Table_1.[UNIQUE #] = Table_2.[UNIQUE #] AND
Table_1.[SEQ ORDER #] = Table_2.[SEQ ORDER #]
Thank you
February 16, 2011 at 10:52 am
DELETE dbo.Table_2
WHERE EXISTS
(
SELECT *
FROM dbo.Table_1 T1
WHERE T1.[UNIQUE #] = Table_2.[UNIQUE #]
AND T1.[SEQ ORDER #] = Table_2.[SEQ ORDER #]
)
February 16, 2011 at 11:01 am
Excellent, I'll try it, thank you!
February 16, 2011 at 11:13 am
That appears to delete everything from the tables, not just the related rows...
February 16, 2011 at 11:29 am
Ken's query should work. So should this:
DELETE FROM t2
FROM Table_1 t1
RIGHT OUTER JOIN
dbo.Table_2 t2 ON t1.[UNIQUE #] = t2.[UNIQUE #] AND
t1.[SEQ ORDER #] = t2.[SEQ ORDER #]
WHERE t1.[UNIQUE #] IS NOT NULL
If they are both not working, look more closely at your data, and verify that there are rows in Table_2 that are not related to any row in Table_1
February 16, 2011 at 11:42 am
Yeah, I don't get it either. Tables 1 and 2 have 1007 related rows out of a total of 23056 rows in Table 1. I'll keep playing with it. Perhaps there is something else going on that I'm nott seeing.
February 16, 2011 at 11:50 am
Ok guys, you got it, I was wrong. Thanks a lot!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply