SQLTeam.com | Weblogs | Forums

Changing a SELECT query into a working DELETE query

Transact-SQL
#1

This is my working SELECT query:
SELECT 'dbo.elim' [TableName],COUNT(*)
FROM dbo.elim x JOIN dbo.orderlog o ON x.ordernum = o.orderno
WHERE o.ord_actshp <DATEADD(MM,-25,GETDATE())

I need to turn it into a working DELETE Query with the same criteria.

Thank you for any assistance.

#2

Which table do you want to DELETE FROM??

#3

Hi Scott. TY for the quick response. I need to delete rows from the "dbo.elim" table. That table does not have a "DateStamp" column for me to use to go back 25 months for old data so that is why I am joining two other tables that combined give me reliable parameters to apply against the data in the 'elim' table. All of the tables reside in the same OLTP DB named GSP_PROD01.

#4 
DELETE FROM x
--SELECT 'dbo.elim' [TableName],COUNT(*)
FROM dbo.elim x 
INNER JOIN dbo.orderlog o ON x.ordernum = o.orderno
WHERE o.ord_actshp < DATEADD(MONTH, -25, GETDATE())
1 Like
#5

Thank You Scott. I will test this out and let you know. This should delete millions of ROWS and trim the elim table to a manageable state for better active query performance when done..

#6

Scott, Your Script change worked perfect. TY soooo much.

#7

Scott, Your Script change worked perfect. TY soooo much.