Turning select * from into delete from

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

General SQL Server Forums

New to SQL Server Programming

Turning select * from into delete from

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-04-08 : 20:49:13

The following selects the records i want from the table:


Select * FROM dbo.arrest t1
WHERE arrestno NOT IN (SELECT arrestno FROM newgcsowarrants t2 WHERE t2.arrestno=t1.arrestno)

The following moves those rows into a new table:


Select * into dbo.warrantsnotneeded2 FROM dbo.arrest t1
WHERE arrestno NOT IN (SELECT arrestno FROM newgcsowarrants t2 WHERE t2.arrestno=t1.arrestno)

WHY does the following not, then, delete those rows now moved into the new table OUT of the arrest table????:


Delete from dbo.arrest t1
WHERE arrestno NOT IN (SELECT arrestno FROM newgcsowarrants t2 WHERE t2.arrestno=t1.arrestno)

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-04-08 : 20:52:21

And likewise this approach does not work either:

Delete from dbo.arrest (Select * FROM dbo.arrest t1
WHERE arrestno IN newgcsowarrants)

Help. Please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-08 : 23:27:40

try


Delete t1 from dbo.arrest t1
WHERE arrestno NOT IN (SELECT arrestno FROM newgcsowarrants t2 WHERE t2.arrestno=t1.arrestno)

or simply

Delete from dbo.arrest 
WHERE arrestno NOT IN (SELECT arrestno FROM newgcsowarrants )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-04-10 : 09:22:00

Thanks. This was one of those things that i "knew" which i forgot i "knew". Thanks for the nudge.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 15:24:41

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources