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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy