Help with delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with delete

Author

Topic

natural_orange
Starting Member

1 Post

Posted - 2008-07-24 : 10:39:08

I have two tables. I need to delete from one tables, any rows that don't have a corresponding value in another table.

I have a view that uses a Union to get the results that aren't in the other table, I just don't know how I can delete them...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 10:48:36

DELETE t1
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.FK=t1.PK
WHERE t2.FK IS NULL

this will delete all records in t1 which do not have corresponding values in t2

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-24 : 11:04:44

You can also update the table structure of the table for a Primary Key - Foreign key relationship, with cascading delete. This way when one record is deleted from the Primary table, the corresponding records will be deleted from the Foreign table automatically.

MakeYourDaddyProud

184 Posts

Posted - 2008-07-24 : 11:15:36

Or,

DELETE FROM Table1
WHERE PK NOT IN (SELECT FK FROM Table2)

Are you good enough? Skillprover.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources